5 highest then list names

[Guest]

I've tried several different ways to solve this problem:
I would like to list the names with the 5 highest scores
some are tied for position.
all the formulae I've tried does not result in what I expected
Where am I going wrong??


col A col B
1 John
1 Mary
2 Sue
3 Bob
3 Jester
5 Sam
15 Tina
10 Rex
10 Lowell
4 Grinch

=VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100,1,FALSE)
=VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100,2,FALSE)
15 Tina
10 Rex
10 Rex
5 Sam
4 Grinch
3 Bob
3 Bob
2 Sue
1 John
1 John

gives me duplicates of same number in col A ( will not find next number down
- eliminates Mary, Jester and Lowell)

=VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A)
gives me only the MAX with the name

I have even used CONCATENATE to solve it, but that did not work either

what to do???
Thanks for any help you can offer.

 

[Domenic]

Assuming that A2:B11 contains your data, try the following...

C2, copied down:

=RANK(A2,$A$2:$A$11)+COUNTIF($A$2:A2,A2)-1

D1: enter 5, indicating that you want a Top 5 list

*Change this number according to the desired Top N list

E1:

=MAX(IF(A2:A11=INDEX(A2:A11,MATCH(D1,C2:C11,0)),C2:C11))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

F2, copied down and across:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$11,MATCH(ROWS(F$2:F2),$C$2:$C$11,
0)),"")

Hope this helps!

 

[Bob Phillips]

Try this array formula to get the name

=INDEX($B$1:$B$20,MATCH(LARGE($A$1:$A$20+1/(ROW($A$1:$A$20)*10^10),ROW(A1)),
$A$1:$A$20+1/(ROW($A$1:$A$20)*10^10),0))

as an array formula, you need to commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Rosenfeld]

I've tried several different ways to solve this problem:
I would like to list the names with the 5 highest scores
some are tied for position.
all the formulae I've tried does not result in what I expected
Where am I going wrong??


col A col B
1 John
1 Mary
2 Sue
3 Bob
3 Jester
5 Sam
15 Tina
10 Rex
10 Lowell
4 Grinch

=VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100,1,FALSE)
=VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100,2,FALSE)
15 Tina
10 Rex
10 Rex
5 Sam
4 Grinch
3 Bob
3 Bob
2 Sue
1 John
1 John

gives me duplicates of same number in col A ( will not find next number down
- eliminates Mary, Jester and Lowell)

=VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A)
gives me only the MAX with the name

I have even used CONCATENATE to solve it, but that did not work either

what to do???
Thanks for any help you can offer.

One way to do this is to sort the table by scores, descending.

If you want a formulaic version, you can download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr/

Then, with your two column range named "rng", and the column of scores named
"scores", enter these formulas in two adjacent cells:

E1:
=IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)>=LARGE(
Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),1),"")

F1:
=IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)>=LARGE(
Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),2),"")


Then select both cells and copy/drag down no further than the total number of
entries in Scores (dragging further will give a #REF! result).




--ron

 

[Guest]

It 'sorta' worked for me
I dragged it past the Scores before it came up with any names
(E1)
it gave me the names --- great
the one for (F1) did not work, whichever way I tried.
But no loss here, the names were in the correct order I added a new column
to the right of the names which equaled the column to the left of the names
(so I can use VLOOKUP)
which I have done and it works great
Thank you all
I will no less be tinkering around with a copy of the sheert to further
investigate.

 

[Ron Rosenfeld]

It 'sorta' worked for me
I dragged it past the Scores before it came up with any names
(E1)
it gave me the names --- great
the one for (F1) did not work, whichever way I tried.
But no loss here, the names were in the correct order I added a new column
to the right of the names which equaled the column to the left of the names
(so I can use VLOOKUP)
which I have done and it works great
Thank you all
I will no less be tinkering around with a copy of the sheert to further
investigate.

That doesn't make sense to me.

The formula in E1 should be giving you the highest score, not the name.

I suspect you have either made a typo in the formula, or not NAME'd the ranges
properly; or perhaps you do not have things set up as you posted with the
Scores in column A and the Names in Column B.

Try these:

E1:
=IF(INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1),1)>=LARGE(
$A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1),1),"")

F1:
=IF(INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1),1)>=LARGE(
$A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1),2),"")

The only difference between the two formulas is that the last digit (in the
second line) in one is a '1', and in the other it is a '2'. This reflects
whether it should be picking up the data from the first column (score column)
or second column (name column) of your two column data table.


--ron