ranking

numberonetwin

New Member
Joined
Jul 24, 2007
Messages
49
what formula would be used to rank scores if you had the following



name score
participant 1 14
participant 2 27
Participant 3 19


to get this result

Participant 2 27
Participant 3 19
Participant 1 14
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If you want a formula solution do this:
Excel Workbook
ABC
1namescore
2participant 1143
3participant 2271
4Participant 3192
Sheet


Looks like you just sorted the range by the score column descending for your result.
 

numberonetwin

New Member
Joined
Jul 24, 2007
Messages
49
is there a formula that would rank the names to correspond with the order of ranking from highest to lowest as well?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
A formula can not change the arrangement of your data on the page.

If you want to SORT the data:
Select the data,
Click "Data" in the standard menu bar
Click SORT
Under "Sort by", choose the column you want to use, (score)
Choose "Descending"
OK
 

numberonetwin

New Member
Joined
Jul 24, 2007
Messages
49
so in other words, you have to manually sort the information after each data input...

thanks for your help!
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
You can automate the SORT with VBA code.
Ideally, if you use code to add new data, you could add a sort routine to that code.
This code will SORT data that is congruent to cell A2.
Code:
    Range("A2").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Column B is used as SORT criteria.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"
Code:
Sub SortData()
    Range("A2").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess _ 
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal
End Sub
How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialog box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Maybe...
Book3
ABCDE
1namescore
2participant 114participant 227
3participant 227Participant 319
4Participant 319participant 114
5
6
Sheet1


D2:

Control+shift+enter...

=IF(ROWS($D$3:D3)<=ROWS($A$2:$A$4),INDEX($A$2:$A$4,SMALL(IF($B$2:$B$4=E2,ROW($B$2:$B$4)-ROW($B$2)+1),COUNTIF($E$2:E2,E2))),"")

then copy down.

E2, copy down:

=LARGE($B$2:$B$4,ROWS($E$3:E3))
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top