Rank Adjacent Cells

[tcarwardine]

Hi everyone

I have a list of scores for students along with year group they are studying in.

Name	Points	Year
John	100	7
Paul	252	8
George	800	8
Ringo	53	7

<tbody>
</tbody>

I would like to create a leader board for the Top 10 in each year group based on these scores.
Year 7

Position	Points	Name
1	100	John
2	53	Ringo

<tbody>
</tbody>

Year 8

Position	Points	Name
1	800	George
2	252	Paul

<tbody>
</tbody>

Is there any formula I could use to put something like this together?

Thanks
Tom

 

[FormR]

Hi, see if you can adapt something like this to your set-up:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1					Year	7			Year	8
2	Name	Points	Year		Position	Points	Name		Position	Points	Name
3	John	100	7		1	100	John		1	800	George
4	Paul	252	8		2	53	Ringo		2	252	Paul
5	George	800	8
6	Ringo	53	7
Rebate

Cell Formulas
Range	Formula
F3	=AGGREGATE(14,6,$B$3:$B$100/($C$3:$C$100=F$1),ROWS(F$3:F3))
G3	{=INDEX($A$3:$A$100,SMALL(IF($B$3:$B$100=F3,IF($C$3:$C$100=F$1,ROW($C$3:$C$100)-ROW($C$3)+1)),COUNTIF(F$3:F3,F3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.