MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort and display the "Top X"


Posted by Gerhard Seljehammer on December 29, 2000 6:31 AM

I have a table with two columns. Name and value. Very simple: (but not for me), I just want to sort it first by name and then by value (that is no problem) and then only display the X greatest values for each name.

Anyone has an idea?


Posted by Aladin Akyurek on December 29, 2000 7:57 AM

I see some mismatch of meaning between your "Top X" and "only display X greatest values for each name."

If each name is associated with a number (e.g., a score on something) and you want to retrieve the names associated with, say 3, highest numbers, there are 2 issues to consider:

A) Are the numbers unique? If so, the solution is relatively easy to produce.

B) A is false. That is, a given number (score) is associated with 2 or more names. Your question is then identical to questions like "Give the names of the employees who earn the smallest salary." This type of questions should be answered preferably with database functions.

Aladin

Posted by Dave on January 01, 2001 3:11 AM

Hi

You could apply Autofilters to both columns then filter by a particular name and then by "Top 10" in the Value column.

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on January 01, 2001 10:41 PM

In case B is true, a formula-based solution is also possible.

Aladin

Posted by Gerhard on January 03, 2001 1:22 AM

Case B is true. And to rephrase my question; I want the X greatest values for each name listed at the same time. Autofilter (dave) is not good enough cause it gives me only one name at the time.
(Thank you for trying to help me, boys)
Gerhard

Posted by Aladin Akyurek on January 05, 2001 12:08 AM

Hi Gerhard,

Here is a solution to your problem, which has become possible after an additional clarification thru e-mail.
But, first things first.

Here is your example with the expected results (as you provided thru e-mail).

Name Value
Ole 2
nils 3
petter 5
karl 3
Ole 5
nils 6
petter 3
karl 2
Ole 7
nils 1
petter 8
Ole 4
nils 5
petter 7
Ole 9
nils 4
petter 3
Ole 6
nils 8
petter 9
karl 3
Ole 4
nils 6
petter 5
karl 3
Ole 2
nils 7
petter 8

The above data (including the labels) occupy the range A1 thru B29.
You want highest N (or X) values by name. For the example you've set N to 3.

Expected outcome:

karl 3
karl 3
karl 3
nils 8
nils 7
nils 6
Ole 9
Ole 7
Ole 6
petter 9
petter 8
petter 8

Here is a machinery to compute this outcome.

Step 1. Put the value of N in some cell and name it MaxTop.

Step 2. Sort the names first, say in column D. [ This sorting is formula-based (which I gloss over here), but it can also be done via Data,Sort. ]

Step 3. Create list of names from D in E, where duplicates are removed. [ The removal of duplicates is formula-based, but I will not eloborate here on the machinary that is needed. ]

After this step we have:

E2 karl
E3 niels
E4 Ole
E5 petter

Step 4. Array-enter (that is, hit control+shift+enter to enter) the following formula [ the jewel, I guess ]

F2 =IF(COLUMN()-COUNTA($A2:$E2)<=MaxTop,LARGE(IF($A2:$A29=$E2,1,0)*($B2:$B29),COLUMN()-COUNTA($A2:$E2)),"")

Copy this down and across as far as needed.

Here is what we get, populating the range E2 thru H5:

karl 3 3 3
nils 8 7 6
Ole 9 7 6
petter 9 8 8

PS. The workbook containing all of the machinery including the steps that I didn't include here is underway to you via e-mail.

Aladin