Combine rank values and auto-sort with formula and limited/no helper columns

[Emerlin]

Another fantasy football cheat sheet question. I have two sets of data that I would like to average (by name) and sort. I was hoping to do this without the use of a bunch of helper columns and manual sorting. I put a quick manual table together then sorted it for reference. Any help appreciated!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Combined/Sorted CheatSheet				CheatSheet Source 1				CheatSheet Source 2				Manual
3		Rank	Player Name			Rank	Player Name			Rank	Player Name			Rank	AVG	CS1	CS2	Player Name
4						1	Jalen Hurts			1	Josh Allen			1	2	2	1	Josh Allen
5						2	Josh Allen			2	Lamar Jackson			2	3	1	4	Jalen Hurts
6						3	Patrick Mahomes			3	Patrick Mahomes			3	3	4	2	Lamar Jackson
7						4	Lamar Jackson			4	Jalen Hurts			4	3	3	3	Patrick Mahomes
8						5	Anthony Richardson			5	C.J. Stroud			5	6	5	6	Anthony Richardson
9						6	Joe Burrow			6	Anthony Richardson			6	6	7	5	C.J. Stroud
10						7	C.J. Stroud			7	Jordan Love			7	7	7	7	Jordan Love
11						8	Dak Prescott			8	Kyler Murray			8	8	6	9	Joe Burrow
12						9	Jayden Daniels			9	Joe Burrow			9	9	10	8	Kyler Murray
13						10	Kyler Murray			10	Jayden Daniels			10	10	9	10	Jayden Daniels
14
Sheet1

Cell Formulas
Range	Formula
O4:O13	O4	=AVERAGE(P4:Q4)

 

[Fluff]

I very much doubt you could do that without helper columns in 2016.

 

[Emerlin]

I very much doubt you could do that without helper columns in 2016.

Thank you for your reply! I think I should consider upgrading to the new version.

 

[Fluff]

You can certainly do it with 365

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Combined/Sorted CheatSheet				CheatSheet Source 1				CheatSheet Source 2
3		Rank	Player Name			Rank	Player Name			Rank	Player Name
4		1	Josh Allen	1.5		1	Jalen Hurts			1	Josh Allen
5		2	Jalen Hurts	2.5		2	Josh Allen			2	Lamar Jackson
6		3	Patrick Mahomes	3		3	Patrick Mahomes			3	Patrick Mahomes
7		4	Lamar Jackson	3		4	Lamar Jackson			4	Jalen Hurts
8		5	Anthony Richardson	5.5		5	Anthony Richardson			5	C.J. Stroud
9		6	C.J. Stroud	6		6	Joe Burrow			6	Anthony Richardson
10		7	Jordan Love	7		7	C.J. Stroud			7	Jordan Love
11		8	Joe Burrow	7.5		8	Dak Prescott			8	Kyler Murray
12		9	Dak Prescott	8		9	Jayden Daniels			9	Joe Burrow
13		10	Kyler Murray	9		10	Kyler Murray			10	Jayden Daniels
14		11	Jayden Daniels	9.5
Data

Cell Formulas
Range	Formula
B4:D14	B4	=LET(v,VSTACK(F4:G13,J4:K13),u,UNIQUE(INDEX(v,,2)),HSTACK(SEQUENCE(ROWS(u)),SORT(HSTACK(u,AVERAGEIFS(F4:J13,G4:K13,u)),2)))
Dynamic array formulas.

 

[Emerlin]

You can certainly do it with 365

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Combined/Sorted CheatSheet				CheatSheet Source 1				CheatSheet Source 2
3		Rank	Player Name			Rank	Player Name			Rank	Player Name
4		1	Josh Allen	1.5		1	Jalen Hurts			1	Josh Allen
5		2	Jalen Hurts	2.5		2	Josh Allen			2	Lamar Jackson
6		3	Patrick Mahomes	3		3	Patrick Mahomes			3	Patrick Mahomes
7		4	Lamar Jackson	3		4	Lamar Jackson			4	Jalen Hurts
8		5	Anthony Richardson	5.5		5	Anthony Richardson			5	C.J. Stroud
9		6	C.J. Stroud	6		6	Joe Burrow			6	Anthony Richardson
10		7	Jordan Love	7		7	C.J. Stroud			7	Jordan Love
11		8	Joe Burrow	7.5		8	Dak Prescott			8	Kyler Murray
12		9	Dak Prescott	8		9	Jayden Daniels			9	Joe Burrow
13		10	Kyler Murray	9		10	Kyler Murray			10	Jayden Daniels
14		11	Jayden Daniels	9.5
Data

Cell Formulas
Range	Formula
B4:D14	B4	=LET(v,VSTACK(F4:G13,J4:K13),u,UNIQUE(INDEX(v,,2)),HSTACK(SEQUENCE(ROWS(u)),SORT(HSTACK(u,AVERAGEIFS(F4:J13,G4:K13,u)),2)))
Dynamic array formulas.

Thank you for this - will it work in 2021? I am considering purchasing.

 

[Fluff]

No it won't, but it will probably work with 2024 when it comes out.

 

[Emerlin]

Sounds like I should wait for that - thank you.

 

[Fluff]

You're welcome.

 

[Peter_SSs]

1. How did you get a 7 in cell P10 since Jordan Love does not appear in source 1?
   
   
2. Or if that 7 should be there, why isn't Dak Prescott in the results with an average rank of 8, better than both Kyler Murray and Jayden Daniels?