Pinewood deby sort

[Brad]

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

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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
>

[Ken Hudson]

Hi Brad,
Here is as a macro solution you can try.

With the workbook open, press Alt-F11 to open visual basic.
Go to Insert > Module.
Copy and paste the code below into the module.
Close Visual Basic.
Back on the worksheet go to Tools > Macro > Macros...
Highlight the macro and click the Run button.

Code:

Sub Derby()

Dim Counter As Double
Dim Iloop As Double
Dim NumRows As Double

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Range("C1") = "Place"
Counter = 1
NumRows = Range("A65536").End(xlUp).Row
Range("A2:B" & NumRows).Sort Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = 2 To NumRows
Cells(Iloop, "C") = Counter
If Cells(Iloop, "B") <> Cells(Iloop + 1, "B") Then
Counter = Counter + 1
End If
Next Iloop

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Cheers.

--
Ken Hudson


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

[Brad]

This worked thank you - Happy new year

"Gary L Brown" wrote:

> 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
> gary_brown@ge_NOSPAM.com
> 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
> >