5 highest then list names

[ufo_pilot]

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!


In article <[email protected]>,
ufo_pilot <[email protected]> wrote:

> 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.

[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)

"ufo_pilot" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Ron Rosenfeld]

On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot
<[email protected]> wrote:

>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

[ufo_pilot]

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" wrote:

> On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot
> <[email protected]> wrote:
>
> >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
>

[Ron Rosenfeld]

On Thu, 12 Jan 2006 07:49:06 -0800, ufo_pilot
<[email protected]> wrote:

>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