Ranking Multiple Data Sets

[joeyjoejoejnr]

I'm struggling to explain this but I will try. I'm looking to rank player scores from multiple basketball divisions that all lie within one column.

First Name	Last Name	Division	Points	Ranking
Jim	Adams	10	55	(2)
Joe	Stephens	9	14	(3)
John	Jones	8	45	(1)
Derek	Smith	9	87	(1)
Chris	White	7	54	(2)
Fred	Brown	7	87	(1)
Dave	Nerk	9	41	(2)
Doug	Dobbin	10	67	(1)
Greg	Johnson	8	18	(2)

<tbody>
</tbody>

So as you can see there are 4 player divisions and I need a formula to be able to rank the players based on their associated division.
The data will be unsorted and there is likely to be about 400 player entries across the 4 division.
It will also be quite likely to have multiple players on the same score.
(The data in the brackets are the answers the formula should return - ... yes, captain obvious)

Any thoughts people?

I greatly appreciate all of your expertise
- Joe

 

[63falcondude]

E2 =SUMPRODUCT(($C$2:$C$10=C2)*(D2<$D$2:$D$10))+1

or

E2 =COUNTIFS($C$2:$C$10,C2,$D$2:$D$10,">="&D2)

Source: http://www.mrexcel.com/forum/excel-questions/543546-rank-within-group-within-sub-group-problem.html

 

[dchaney]

Can you not sort the sheet by Division Lowest to Highest then Points Highest to lowest to place all of the Divisions together? then add the following Formula looks like it provides what you need.. I am sure there are better ways, but this seems to work.

=IF(D2<>D1,"1",IF(E2<>E1,F1+1,F1))

First Name	Last Name	Division	Points	Ranking
Fred	Brown	7	87	1
Chris	White	7	54	2
John	Jones	8	45	1
Greg	Johnson	8	18	2
Derek	Smith	9	87	1
Dave	Nerk	9	87	1
Joe	Stephens	9	14	2
Doug	Dobbin	10	67	1
Jim	Adams	10	55	2

<tbody>
</tbody>