Populate array from unique values

Hi everyone

I have a table with thousands of rows with information in several columns. I need to create an array in VBA that hosts on the first column the unique values in the "Group" column, the corresponding values from the "Group Description" column and the number of times that the Group value is repeated in that table. I want the array to be hosted in VBA but I do not want to print it in any cell in my sheet.

Can someone please help me with the code? I attached an excel file as reference. The cells in red show the desired outcome but it's just for reference, I do not want to print it in the sheet.

Thank you!

Here a dictionary approach. To show how to get the data from the dictionary, the output is written to the sheet.

Please Login or Register  to view this content.

For writing to a sheet you just need one click in power query

Please Login or Register  to view this content.

In the days before Power Query, there already were query tables which you could generate like this in VBA (Just for fun to show the different methods)

Please Login or Register  to view this content.

The thing with converting Excel formula's to VBA is that you are not able to evaluate properly.

With evaluating I mean stepping through code, which you can’t with formula’s in vba