swapinfuture
01-22-2013, 08:39 AM
I’m looking for the most efficient VBA solution (processing time) to the below scenario:
In column A, I have 400,000 rows of Strings (referred to as “list”) that could be 1 of about 200 different values (in production this value will fluctuate between 200 and 300). I need to count how many times each String value occurs in the list and store the top 10 occurrences (both the String value and its total number of occurrences) in an array for later use.
I’m considering using the DistinctValues() function to get an array containing a unique list of entries and then loading the original list into an array (to speed up processing) and iterating over it with the unique list; checking for equality and keeping a tally in another array (don’t think I can ReDim the unique array to be 2 dimensional and store the respective count in the same array).
Another solution would be to dump the unique list into column A of another sheet and in column B use the formula =COUNTIF(oringalSheet!A:A, A1). This would provide a layout in which I could easily iterate through and extract the top 10 values.
I’m looking for some community feedback prior to continuing.
In column A, I have 400,000 rows of Strings (referred to as “list”) that could be 1 of about 200 different values (in production this value will fluctuate between 200 and 300). I need to count how many times each String value occurs in the list and store the top 10 occurrences (both the String value and its total number of occurrences) in an array for later use.
I’m considering using the DistinctValues() function to get an array containing a unique list of entries and then loading the original list into an array (to speed up processing) and iterating over it with the unique list; checking for equality and keeping a tally in another array (don’t think I can ReDim the unique array to be 2 dimensional and store the respective count in the same array).
Another solution would be to dump the unique list into column A of another sheet and in column B use the formula =COUNTIF(oringalSheet!A:A, A1). This would provide a layout in which I could easily iterate through and extract the top 10 values.
I’m looking for some community feedback prior to continuing.