Count Values in List, Display Top x

[ChronicFear]

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.

[lucas]

Hi Chronic,
We don't provide complete solutions but would be glad to help you work your way through this. As a first step I would suggest that you consider a sumif formula for the first part....see this (http://vbaexpress.com/forum/showthread.php?t=15564) thread for some examples that might work for you. If that doesn't look like something you can work with then post your questions back here.

[Shazam]

Hi ChronicFear

Assuming your names are in column A input formula in cell B2 and copy down.


=INDEX(A$2:A$21,MATCH(LARGE(IF(MATCH(A$2:A$21,IF($A$2:$A$21<>"",A$2:A$21,0))=ROW(A$2:A$21)-ROW(A$2)+1,COUNTIF(A$2:A$21,A$2:A$21)-ROW(A$2:A$21)/10^5),ROWS(B$2:B2)),COUNTIF(A$2:A$21,A$2:A$21)-ROW(A$2:A$21)/10^5,0))


Note: The formula is an-array need to hold down:

Ctrl,Shift,Enter


Hope it helps!

[lucas]

Cronic,
Please also provide a link to the cross posts. Please read our faq and click here (http://www.excelguru.ca/node/7) for an explaination on cross posting. It is good that you mentioned the cross posts but please provide a link so folks trying to answer your questions can see what has already been done before they repeat someone elses work......

[ChronicFear]

Thanks for the help. I will post my cross links as soon as my post count is high enough. Sorry about not being able to do that up front.

Shazam - I'm not sure what you mean by holding down ctrl+shift+enter. When, where, and why do I do that? When I try simply pasting this formula it outputs #VALUE, but I assume this is because I don't understand the aformentioned instruction.

[Bob Phillips]

When you enter a formula normally, you use Enter to complete it. With Shaz's, you use Ctrl, Shift, and Enter together to finish it.

.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[Shazam]

Shazam - I'm not sure what you mean by holding down ctrl+shift+enter. When, where, and why do I do that? When I try simply pasting this formula it outputs #VALUE, but I assume this is because I don't understand the aformentioned instruction.

The cell that has the #VALUE error double click inside that cell and then hold down Ctrl,Shift,Enter.

[ChronicFear]

Ok, I did that and the formula entered into the cell correctly, but it still returns an error message. Any other suggestions?

[Shazam]

Look at the attachment below.

[david000]

While you can not use any AutoFilter with an array formula you can still achieve your desired results with a Pivot Table...and use a simpler array formula to boot.


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:
This attachement is just a list named "List" and a pivot table with a count