ChronicFear
10-17-2007, 08:57 AM
Hello,
I have three lists of data on one sheet in Excel 2002. They are Name, Location, and Client amount. 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
**Cross posted on Mr.Excel and OzGrid, but no solutions there.
I have three lists of data on one sheet in Excel 2002. They are Name, Location, and Client amount. 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
**Cross posted on Mr.Excel and OzGrid, but no solutions there.