Rank Adjacent Cells

[tcarwardine]

Hi everyone

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Points[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]100[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]252[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]800[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Ringo[/TD]
[TD]53[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

I would like to create a leader board for the Top 10 in each year group based on these scores.
Year 7
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Points[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]53[/TD]
[TD]Ringo[/TD]
[/TR]
</tbody>[/TABLE]

Year 8
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Points[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]800[/TD]
[TD]George[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]252[/TD]
[TD]Paul[/TD]
[/TR]
</tbody>[/TABLE]

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.