Top 10 for each Item in column

[Tod]

I've been trying to do this with autofilter, but I think
now it's going to require greater thinkers than I.

I have two columns. The first is a list of country codes.
The second is amounts. There are many, many amounts and
so there are also many, many instances of country codes.
What I want to know is the top 10 highest amounts for
each country code. So if this is the list:

Country Code Amount
UK 123
US 22
FR 225
BE 101
IT 200
UK 15
UK 3
UK 112
IT 125
BE 234
BE 256
IT 751
FR 52
FR 68
FR 85
FR 885
.......etc

This list goes on for about 15,000 rows. I want to find
out the top 10 highest Amount for each Country Code. How
could I do that?

tod

 

[Myrna Larson]

First get a list of the unique country codes, then use formulas to retrieve
the values.

I assume your original data is in A1:B15000, with headers in row 1:

1. Select all of the data in column A only (not A and B)

2. Use Data/Filter/Advanced and select Unique Records, and Paste to Another
location (say D1). That will give you a list of the unique countries, with
the header "Country Code" in D1, the first country code in D2.

3. In E2, put this array formula

=LARGE(IF($A$2:$A$15000=$D2,$B$2:$B$15000,0),COLUMN()-COLUMN($E2)+1)

This must be entered with CTRL+SHIFT+ENTER, not just ENTER.

4. Copy the formula to the right, through N2.

5. Copy E2:N2 down as far as needed.

For each country, the largest amount is in E, 2nd largest in F, 3rd largest in
G, etc.

 

[Dana DeLouis]

A Pivot table sounds like a good solution to me. Drag the "Country" and
then "Amount" to the "Row" area. One suggestion is to drag "Amount" to the
"Data" area and change it from "Sum" to "Count." (just a way to see how
many are tied for the top spot).

In your Pivot Table, double click the "Amount" heading, then select
"Advanced", Select to Sort in descending order, and select to show "Top 10
Items."

If you have a lot of data, select the Pivot table options, and uncheck "Save
data with table layout" if you wish to store the file as a smaller size.
HTH.

 

[Myrna Larson]

Hi, Dana:

I wanted a Pivot Table, too, but I couldn't find the Top Ten options. Thanks
for the kick in the pants <g>.

I tried your suggestion, with County and Amount as row fields. I was required
to supply a data field, so I used Amount (again), with the Count function.

Then I tried the advanced field settings to show Top Ten. Top Ten has to be
based on a data field, not a row or column field, so my only option was Count
of Amoung. With his sample data, I said to show the top 2. It didn't work!
It's showing the 2 highest counts, not the 2 highest amounts.

Did I misunderstand your instructions (I wouldn't be surprised if I did!)?

To get it to work, I had to summarize the Amount field by Min, Max, or Average
(which would all be the same, of course, given that Amount is also a row
field).

Myrna Larson

 

[Myrna Larson]

I wonder if he needs to worry about the limit on number of items in a pivot
table (8000, IIRC).

Do you know how that limit is calculated? Is it the number of cells in the
data area, before filtering for the "Top N"?

 

[Dana DeLouis]

Myrna. You are absolutely correct! My mistake. I use the Top 10 often,
and made a mistake with this example data. I could have sworn I've done
something similar a few times in the past. I'll experiment and see if there
are any workarounds. Thanks for the catch. :>(

 

[Tom Ogilvy]

sort on Country code and amount (descending)

in an adjacent column (Assume in C2) put in a formula like

=Countif($A$2:A2,A2)

then drag fill down the column by all your data.

Now do a custom autofilter on this column for Less than 11

Copy to another sheet if you wish. This doesn't have any special handling
for ties, especially ties at the 10th highest boundary ( for example if one
country had 12 entries with the highest value as a simple example - only 10
would be shown).