Assumptions:
Column A, starting in A1 has Heading ('Name') and names
Column B, starting in B1 has Heading ('Score') and scores
Total range of data is A1:B10.

Actions:
1) Put a Heading ('Place') in C1
2) Put a Heading ('Top Scores') in E1
3) Put a Heading ('Place') in F1
4) In F2 put 1 - for 1st place
5) In F3 put 2 - for 2nd place
6) In F4 put 3 - for 3rd place
7) In E2:E4, we need to create array formulas for 1st, 2nd and 3rd place
- In E2 put...

=SMALL(IF(MATCH($B$2:$B$10,$B$2:$B$10,0)=ROW($B$2: $B$10)-CELL("Row",$B$2:$B$10)+1,$B$2:$B$10),1)
- In E3 put...

=SMALL(IF(MATCH($B$2:$B$10,$B$2:$B$10,0)=ROW($B$2: $B$10)-CELL("Row",$B$2:$B$10)+1,$B$2:$B$10),2)
- In E3 put...

=SMALL(IF(MATCH($B$2:$B$10,$B$2:$B$10,0)=ROW($B$2: $B$10)-CELL("Row",$B$2:$B$10)+1,$B$2:$B$10),3)

*** Remember that these are array formulas, instead of hitting ENTER, you
must hit CTRL-SHIFT-ENTER so that the formula has { and } surrounding it!!!
*** Watch the 'wrapping' in this message when copying the formulas.

8) In C2:C10, we want to lookup the table we just created in E1:F4
- In C2 put...

=IF(ISNA(VLOOKUP(B2,$E$1:$F$4,2,FALSE)),99,VLOOKUP (B2,$E$1:$F$4,2,FALSE))
9) Copy the forumla in in C2 down to C10
10) DONE!!!!

Your worksheet should now look something like...
<A <B <C <E <F
Name Score Place Top Scores Place
Billy 22 1 22 1
Tommy 22 1 24 2
Peter 25 3 25 3
Paul 24 2
Johnny 36 99
Steve 29 99
Jerry 26 99
Andy 24 2
Sam 28 99


Credit thanks to Bob Phillips in an October posting for the solution.
----------------------------------
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Brad" wrote:

I have a Excel workbook that will provide a listing of boys and there total
score (1 for first place, 2 for second place, ...

I can manually resort the information to find out who finished first,
second, ...
But I would rather have Excel do this for me automatically, as shown below,
If everyone has the same score everyone finishes in first place.

Thanks in advance for your help!

Name Score
Billy 22
Tommy 22
Peter 25
Paul 24
Johnny 36
Steve 29
Jerry 26
Andy 24
Sam 28

Name Score Place
Billy 22 1
Tommy 22 1
Paul 24 2
Andy 24 2
Peter 25 3
Jerry 26 99
Sam 28 99
Steve 29 99
Johnny 36 99