Rank from highest to lowest

[masterton]

Highest score wins.
We determine who is the champion(s), 2nd, 3rd winner etc.
If two winners have the same highest score, both are 1st winner. The next will be 3rd winner.
If three winners have the same highest score, three are 1st winner. The next will be 4th winner.
The same logic applies to 2nd, 3rd winners and so on.

For example:
Score of different competitors
John || Mary || Sam || Susan || Helen
10 || 8 || 8 || 6 || 3

Champion: John
2nd winner: Mary, Sam
3rd winner: --
4th winner: Susan
5th winner: Helen

How could I write a series of formula which can rank and list the winners automatically?

Click to see the sample sheet for an overview and example.

Thanks a lot for your answer. ^_^

 

[Andrew Poulsom]

Have a look here:

Mr Excel & excelisfun Trick 58.5: Sorting Formula For Biggest Sales and Sales Rep Names with Ties - YouTube

 

[Snakehips]

masterton,

I see from your sample sheet that you are concatenating the names of tied winners, within the same cell.

My first thought is that it will be difficult for you achieve this with a formula, even if you only have five competitors.

Here is a UDF that might help you get the job done.

In the vba editor, (Alt-F11), insert a code module and copy in the below code....

Function WiNames(NameRng As Range, ScoreRng As Range, Place As Integer) As String
For Each cell In ScoreRng
c = c + 1
If WorksheetFunction.Rank(cell, ScoreRng, 0) = Place Then
WName = NameRng(1, c)
If WiNames = "" Then
WiNames = WiNames & WName
Else
WiNames = WiNames & ", " & WName
End If
End If
Next cell
If WiNames = "" Then WiNames = "  --"
End Function

The arguments are
NameRng the range that contains the competitors names
ScoreRng the range that contains the competitors names
Place the winning place you require names for

Below is an example of how I have used it with your sample data set but with an extra two competitors so that you can see how you can extend it to as many competitors as you wish and only return as many winning places as you wish.
Note that the formula in B2 will copy across and down. I have used the COLUMNS() function to give an incrementing value for Place as the formula is copied to the right.

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Event	Champion	2nd	3rd	4th	5th	John	Mary	Sam	Susan	Helen	Tom	Charlie
2	AA	John	Mary, Sam	--	Susan	Helen, Charlie	10	8	8	6	3	2	3
3	BB	Mary, Susan	--	John	Sam, Helen	--	6	9	4	9	4	2	1
4	CC
Sheet5

Cell Formulas
Range	Formula
B2	=IFERROR(WiNames($G$1:$M$1,$G2:$M2,COLUMNS($B2:B2)),"")

Hope that helps.

 

[masterton]

Have a look here:

Mr Excel & excelisfun Trick 58.5: Sorting Formula For Biggest Sales and Sales Rep Names with Ties - YouTube

Thanks a lot. I'm watching it right now.

By the way is that sheet available for download?

 

[masterton]

masterton,

I see from your sample sheet that you are concatenating the names of tied winners, within the same cell.

My first thought is that it will be difficult for you achieve this with a formula, even if you only have five competitors.

Thanks a lot.

There will be more than 5 competitors in the real case.
It would be the best if it is achievable without macro.