Counting values in list and displaying top 3

[ChronicFear]

Hello,

I have three lists of data on one sheet in Excel 2002. They are Name, Location, and Client. Lets say they are in columns A, B, and C. Each row contains information about a sale.

Ex.
Dave Florida MiniMart
Mark New York BigMart


I am trying to write a macro that will look at each column independently, count the number of times a value appears, and then display the top three values. So if Dave's name appears 5 times, Mark's 1 time, and Susan's 3 time, the macro will output their names onto a different worksheet in the order

Dave
Susan
Mark

and ideally with the counted value next to them

Dave 5
Susan 3
Mark 1


I've tried using a pivot table and the auto filter, but those seem to look at the whole row as a discreet value and I want it to ignore the other columns. So eventually it would look something like this:

Dave 5 Florida 4 BigMart 6
Susan 3 Illinois 3 SmallMart 2
Mark 1 New York 2 MidMart 1


For an added challenge, the primary list will be updated with new data every time there is a sale so the macro should first look to see how many entries are in each column and make sure it captures all of them before it runs. And if you'd really like to be my hero, I'd like the macro to run automatically whenever the second worksheet (the statistics page) is selected.

Thank you so much to whomever can help me!

Sincerely,

ChronicFear


***Also posted here: http://www.ozgrid.com/forum/showthread.php?t=78455

[Paul]

Hi Chronic,

Take a look at the attached example of my solution. It does a few things:
Sheet1 contains the data you're working with in columns A through C. Columns E, G and I will receive the unique entries from columns A, B and C once the macro is run. Columns F, H and J have formulas in them to count the number of times each unique entry occurs in their respective columns.

The formula is filled down about 30 rows, and you can fill it down as far as you think you'll need for unique values. I applied conditional formatting on the Count columns (F, H and J) so that if a value is less than 1, make the font white. I set it to less than one, because the formulas in F, H and J don't return whole numbers, they return whole numbers plus a very small decimal based on the row. So if Bob appears 7 times, his count will be 7 plus the row number/10000 that the formula is in. This results in 7.0002, or 5.0018 for someone in the 18th row. I did it this way to easily pick out the 1st, 2nd and 3rd largest values when ties occurred, otherwise if Bob and Tom both have 7, the 1st and 2nd largest value lookups would both return Bob.

Sheet2 holds the formulas for each of the top 3 per category based on the data in Sheet1!E:J.

I also added a command button to Sheet1 to clear columns E, G and I so you don't have to manually. Anytime you want to paste new data into columns A:C just click that first.

I also protected the sheet so the formulas in F, H and J won't be deleted (no password).

I hope that gives you some ideas.

PS - The filter code and commandbutton code are shown below (filter code from worksheet2's activate event, of course):

Please Login or Register  to view this content.

Please Login or Register  to view this content.