| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pinewood deby sort
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pinewood deby sort
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pinewood deby sort
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pinewood deby sort
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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select rows and sort based on type | Excel Discussion (Misc queries) | |||
| data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
| sorting more than 3 keys | Excel Discussion (Misc queries) | |||
| "-" ignored in sort | Excel Discussion (Misc queries) | |||
| HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Linear Mode
