Sort and display the "Top X"

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

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

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

Aladin

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

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