ranking

[numberonetwin]

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

 

[numberonetwin]

just not sure about what to put in my range in the formula

so if my chart looked like this
cellb-------cellc------cellld

name-----score-----rank

player ----15---------1
player-----14---------2


what would my range be to sort the player, score and rank?

 

[Datsmart]

If your data is layed out like this:

Excel Workbook
	A	B	C	D
1		Name	Score	Rank
2		participant 1	14	3
3		participant 2	27	1
4		Participant 3	19	2
Sheet

This code will sort by Score:

Sub SortByScore()
' SORT range congruent to B2, descending, by Column C
    Range("B2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

This code uses "CurrentRegion", which sets the range to sort to all cells "congruent" to cell B2. That is, all cells adjacent to B2.
Click B2 and then hit Ctrl-Shift-*
This will show you what all is being used in the Sort.
It will select all cells adjacent to B2 until there is an empty row/column.

 

[numberonetwin]

thanks so much john.
thats amazing!

worked like a charm...
starting to wrap my head around this a little now!

 

[KiwiITSupport]

I know this is a very old post but it has the exact thing I am looking for (Aladin's reply), where the name and data are in 2 cells, I want it displayed in another set of cells showing the highest score and attaching the persons name.

Here is my example - I would love it if someone could generate the exact code for me. I'll use the cell letters which are in my spreadsheet. Note the data begins from row 28 (eg A28, AA28) and ends on row 44.

A AA
Bilal 96
Jack 192
Philip 317
Ricky 148
Sandeep 114
Simon 30
Andrew 114
Daniel 142
Edmund 58
Jihan 265
Kaizad 164
Matthew 402
Reuben 61
Sanjay 116
Steve 290
Sunday 140
Tony 256

I would like the results displayed in AG for the ranked name, and AH for the ranked score.

Hope that makes sense.

 

[KiwiITSupport]

Can anyone help?