PDA

View Full Version : Unique List and tally from Rows



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.

CodeNinja
01-22-2013, 01:13 PM
Not really sure if this helps, because array formulas take up so much calculation time, but you could look them up with the following formula:

{=index(rng,Match(Large(Countif(rng,list),rows($MyRow$MyCol:MyrowMycol)),Co untif(rng,rng),0))}

This applies where rng = the range A:A, list = an array of the possible answers, and myrow and mycol would be like $B$1:B1 or whatever cell you put this into. Then control shift enter for the array, and copy down as many as you want to display.

You could of course use an if to blank out the result if lets say it is greater than 10... ie =if(Rows($myrow$mycol:myrowmycol)>10,"",formula)


Again, I am not sure of how this would affect your speed because it is an array formula, but it does extract those top 10 for you.

Good luck,

CodeNinja.