List players based on scores in order per year

[vampsthevampyre]

Afternoon All,

Can anybody help I'm looking to produce a table that has the members ranked by score for each year.

The year is dynamically created as is the main array data, and I have manually created the first, second and last year in the lower table but I'm looking to automate the production of the final table.

Any help would be appreciated

Regards

Ian

Premium bonds.xlsx
	BF	BG	BH	BI	BJ	BK	BL
9
10			2021	2022	2023	2024
11		Ian	25	450	1300	800
12		Jennie	0	675	1650	825
13		Daniel	0	875	2500	775
14		David	0	500	1525	575
15
16
17			2024	2023	2022	2021
18		Ian	800	1300	450	25
19		Jennie	825	1650	675	0
20		Daniel	775	2500	875	0
21		David	575	1525	500	0
22
23			2024	2023	2022	2021
24			Jennie	Daniel		Ian
25			Ian	Jennie		Jennie
26			Daniel	David		Daniel
27			David	Ian		David
28
Setup

Cell Formulas
Range	Formula
BH10:BK10	BH10	=BH2#
BG11:BG14	BG11	=Table2[Column headers]
BH11:BK14	BH11	=OFFSET(Results!BL9,,,4,BF4)
BH17:BK17	BH17	=BH1#
BG18:BG21	BG18	=BG11#
BH18:BK21	BH18	=INDEX(BH11#,MATCH(BG18#,BG11#,0),MATCH(BH17#,BH10#,0))
BH23:BK23	BH23	=BH1#
Dynamic array formulas.

 

[Sanjeev1976]

You can try :

Book2
	B	C	D	E	F
2		2021	2022	2023	2024
3	Ian	25	450	1300	800
4	Jennie	0	675	1650	825
5	Daniel	0	875	2500	775
6	David	0	500	1525	575
7
8
9		2024	2023	2022	2021
10	Ian	800	1300	450	25
11	Jennie	825	1650	675	0
12	Daniel	775	2500	875	0
13	David	575	1525	500	0
14
15		2024	2023	2022	2021
16		Jennie	Daniel	Daniel	Ian
17		Ian	Jennie	Jennie	Jennie
18		Daniel	David	David	Daniel
19		David	Ian	Ian	David
Sheet1

Cell Formulas
Range	Formula
D9:F9,D15:F15	D9	=C9-1
B10:B13	B10	=B3:B6
C16:F19	C16	=SORTBY($B10#,C$10:C$13,-1)
Dynamic array formulas.

 

[vampsthevampyre]

You can try :

Book2
	B	C	D	E	F
2		2021	2022	2023	2024
3	Ian	25	450	1300	800
4	Jennie	0	675	1650	825
5	Daniel	0	875	2500	775
6	David	0	500	1525	575
7
8
9		2024	2023	2022	2021
10	Ian	800	1300	450	25
11	Jennie	825	1650	675	0
12	Daniel	775	2500	875	0
13	David	575	1525	500	0
14
15		2024	2023	2022	2021
16		Jennie	Daniel	Daniel	Ian
17		Ian	Jennie	Jennie	Jennie
18		Daniel	David	David	Daniel
19		David	Ian	Ian	David
Sheet1

Cell Formulas
Range	Formula
D9:F9,D15:F15	D9	=C9-1
B10:B13	B10	=B3:B6
C16:F19	C16	=SORTBY($B10#,C$10:C$13,-1)
Dynamic array formulas.

Thanks works perfectly, but do you know if there is a way to automate it, so it grows with the dynamic data?

 

[Sanjeev1976]

Try, it will automatically grow with your dynamic data. The only thing you need to take care is to add further rows wherever required.