software

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)
15Tina
10 Rex
10 Rex
5 Sam
4Grinch
3Bob
3Bob
2Sue
1John
1John

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)amp;quot; quot;amp;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.

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)lt;=$D$1 $E$1,INDEX(A$2:A$11,MATCH(R OWS(F$2:F2),$C$2:$C$11,
0)),quot;quot;)

Hope this helps!In article gt;,
ufo_pilot gt; wrote:

gt; I've tried several different ways to solve this problem:
gt; I would like to list the names with the 5 highest scores
gt; some are tied for position.
gt; all the formulae I've tried does not result in what I expected
gt; Where am I going wrong??
gt;
gt;
gt; col A col B
gt; 1 John
gt; 1 Mary
gt; 2 Sue
gt; 3 Bob
gt; 3 Jester
gt; 5 Sam
gt; 15 Tina
gt; 10 Rex
gt; 10 Lowell
gt; 4 Grinch
gt;
gt; =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE)
gt; =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE)
gt; 15Tina
gt; 10 Rex
gt; 10 Rex
gt; 5 Sam
gt; 4Grinch
gt; 3Bob
gt; 3Bob
gt; 2Sue
gt; 1John
gt; 1John
gt;
gt; gives me duplicates of same number in col A ( will not find next number down
gt; - eliminates Mary, Jester and Lowell)
gt;
gt; =VLOOKUP(MAX(A:A),A:B,2,FALSE)amp;quot; quot;amp;MAX(A:A)
gt; gives me only the MAX with the name
gt;
gt; I have even used CONCATENATE to solve it, but that did not work either
gt;
gt; what to do???
gt; Thanks for any help you can offer.

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)

quot;ufo_pilotquot; gt; wrote in message
...
gt; I've tried several different ways to solve this problem:
gt; I would like to list the names with the 5 highest scores
gt; some are tied for position.
gt; all the formulae I've tried does not result in what I expected
gt; Where am I going wrong??
gt;
gt;
gt; col A col B
gt; 1 John
gt; 1 Mary
gt; 2 Sue
gt; 3 Bob
gt; 3 Jester
gt; 5 Sam
gt; 15 Tina
gt; 10 Rex
gt; 10 Lowell
gt; 4 Grinch
gt;
gt; =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE)
gt; =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE)
gt; 15 Tina
gt; 10 Rex
gt; 10 Rex
gt; 5 Sam
gt; 4 Grinch
gt; 3 Bob
gt; 3 Bob
gt; 2 Sue
gt; 1 John
gt; 1 John
gt;
gt; gives me duplicates of same number in col A ( will not find next number
down
gt; - eliminates Mary, Jester and Lowell)
gt;
gt; =VLOOKUP(MAX(A:A),A:B,2,FALSE)amp;quot; quot;amp;MAX(A:A)
gt; gives me only the MAX with the name
gt;
gt; I have even used CONCATENATE to solve it, but that did not work either
gt;
gt; what to do???
gt; Thanks for any help you can offer.
On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot
gt; wrote:

gt;I've tried several different ways to solve this problem:
gt;I would like to list the names with the 5 highest scores
gt;some are tied for position.
gt;all the formulae I've tried does not result in what I expected
gt;Where am I going wrong??
gt;
gt;
gt;col A col B
gt;1 John
gt;1 Mary
gt;2 Sue
gt;3 Bob
gt;3 Jester
gt;5 Sam
gt;15 Tina
gt;10 Rex
gt;10 Lowell
gt;4 Grinch
gt;
gt;=VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$10 0,1,FALSE)
gt;=VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$10 0,2,FALSE)
gt;15Tina
gt;10 Rex
gt;10 Rex
gt;5 Sam
gt;4Grinch
gt;3Bob
gt;3Bob
gt;2Sue
gt;1John
gt;1John
gt;
gt;gives me duplicates of same number in col A ( will not find next number down
gt;- eliminates Mary, Jester and Lowell)
gt;
gt;=VLOOKUP(MAX(A:A),A:B,2,FALSE)amp;quot; quot;amp;MAX(A:A)
gt;gives me only the MAX with the name
gt;
gt;I have even used CONCATENATE to solve it, but that did not work either
gt;
gt;what to do???
gt;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 xcell05.free.fr/

Then, with your two column range named quot;rngquot;, and the column of scores named
quot;scoresquot;, enter these formulas in two adjacent cells:

E1:
=IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)gt;=LARGE(
Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),1),quot;quot; )

F1:
=IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)gt;=LARGE(
Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),2),quot;quot; )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

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.

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot
gt; gt; wrote:
gt;
gt; gt;I've tried several different ways to solve this problem:
gt; gt;I would like to list the names with the 5 highest scores
gt; gt;some are tied for position.
gt; gt;all the formulae I've tried does not result in what I expected
gt; gt;Where am I going wrong??
gt; gt;
gt; gt;
gt; gt;col A col B
gt; gt;1 John
gt; gt;1 Mary
gt; gt;2 Sue
gt; gt;3 Bob
gt; gt;3 Jester
gt; gt;5 Sam
gt; gt;15 Tina
gt; gt;10 Rex
gt; gt;10 Lowell
gt; gt;4 Grinch
gt; gt;
gt; gt;=VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$10 0,1,FALSE)
gt; gt;=VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$10 0,2,FALSE)
gt; gt;15Tina
gt; gt;10 Rex
gt; gt;10 Rex
gt; gt;5 Sam
gt; gt;4Grinch
gt; gt;3Bob
gt; gt;3Bob
gt; gt;2Sue
gt; gt;1John
gt; gt;1John
gt; gt;
gt; gt;gives me duplicates of same number in col A ( will not find next number down
gt; gt;- eliminates Mary, Jester and Lowell)
gt; gt;
gt; gt;=VLOOKUP(MAX(A:A),A:B,2,FALSE)amp;quot; quot;amp;MAX(A:A)
gt; gt;gives me only the MAX with the name
gt; gt;
gt; gt;I have even used CONCATENATE to solve it, but that did not work either
gt; gt;
gt; gt;what to do???
gt; gt;Thanks for any help you can offer.
gt;
gt; One way to do this is to sort the table by scores, descending.
gt;
gt; If you want a formulaic version, you can download and install Longre's free
gt; morefunc.xll add-in from xcell05.free.fr/
gt;
gt; Then, with your two column range named quot;rngquot;, and the column of scores named
gt; quot;scoresquot;, enter these formulas in two adjacent cells:
gt;
gt; E1:
gt; =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)gt;=LARGE(
gt; Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),1),quot;quot; )
gt;
gt; F1:
gt; =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)gt;=LARGE(
gt; Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),2),quot;quot; )
gt;
gt;
gt; Then select both cells and copy/drag down no further than the total number of
gt; entries in Scores (dragging further will give a #REF! result).
gt;
gt;
gt;
gt;
gt; --ron
gt;

On Thu, 12 Jan 2006 07:49:06 -0800, ufo_pilot
gt; wrote:

gt;It 'sorta' worked for me
gt;I dragged it past the Scores before it came up with any names
gt;(E1)
gt;it gave me the names --- great
gt;the one for (F1) did not work, whichever way I tried.
gt;But no loss here, the names were in the correct order I added a new column
gt;to the right of the names which equaled the column to the left of the names
gt;(so I can use VLOOKUP)
gt;which I have done and it works great
gt;Thank you all
gt;I will no less be tinkering around with a copy of the sheert to further
gt;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)gt;=LARGE(
$A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100 ),ROWS($1:1),1),quot;quot;)

F1:
=IF(INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1 ),1)gt;=LARGE(
$A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100 ),ROWS($1:1),2),quot;quot;)

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