Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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. -- Dana DeLouis Win XP & Office 2003 "Tod" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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" wrote in message ... 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. -- Dana DeLouis Win XP & Office 2003 "Tod" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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. :( -- Dana DeLouis Win XP & Office 2003 "Myrna Larson" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Top 10 for each Item in column
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). -- Regards, Tom Ogilvy "Tod" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The most repeted item in a column? | Excel Discussion (Misc queries) | |||
how to add .12 cents to every item in a column | Excel Discussion (Misc queries) | |||
does item match any in column B | Excel Discussion (Misc queries) | |||
Counting item in a column | Excel Discussion (Misc queries) | |||
Returning the last item in column | New Users to Excel |