# ranking

#### numberonetwin

##### New Member
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
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
is there a formula that would rank the names to correspond with the order of ranking from highest to lowest as well?

#### numberonetwin

##### New Member
that formula works great though for ranking without order...thanks

#### Datsmart

##### Well-known Member
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
so in other words, you have to manually sort the information after each data input...

#### Datsmart

##### Well-known Member
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.

#### numberonetwin

##### New Member
I think i will just stick with the manual! don't know much about vba

thanks!

#### Datsmart

##### Well-known Member
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()
, 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.

##### MrExcel MVP
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))

Replies
1
Views
97
Replies
3
Views
192
Replies
1
Views
67
Replies
1
Views
60
Replies
0
Views
218

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.

### Which adblocker are you using?

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

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