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),COLUM N()-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.


On Mon, 18 Oct 2004 12:01:14 -0700, "Tod" wrote:

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