Formula needed to filter and sort data from range - Excel 2016.

[Emerlin]

Hi - I am working on a fantasy football cheat sheet. I have a large list ranked player (source data in sample sheet) the contain all positions, QB, RB, WR, etc. in a huge list. I would like to rearrange that source data into a "cheat sheet" by position. Is there a formula that can sort through through my data and return the values either by overall rank or positional rank? So WR then POSRank1, POSrank2, etc... Not sure the best way to go about this. Excel 2016 here. Thanks for any help.

p.s. I know there is a newer version of excel that may make this easier - should I consider upgrading?

RVRedraftRankings (1).csv
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team	PosRank
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF	1
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL	1
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA	2
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ	2
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL	3
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN	3
10		7			Chris Olave			7	Justin Jefferson	WR	MIN	4
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET	5
12		9			Drake London			9	Garrett Wilson	WR	NYJ	6
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET	4
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET	1
15		12			Marvin Harrison			12	Trey McBride	TE	ARI	2
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND	5
17		14						14	Chris Olave	WR	NO	7
18		15						15	Deebo Samuel	WR	SF	8
19								16	Drake London	WR	ATL	9
20								17	A.J. Brown	WR	PHI	10
21								18	Cooper Kupp	WR	LAR	11
22								19	Marvin Harrison	WR	ARI	12
23								20	Malik Nabers	WR	NYG	13
24
RVRedraftRankings (1)

 

[Fluff]

Excel 2016 here.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Emerlin]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

done thanks

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team	PosRank
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF	1
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL	1
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA	2
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ	2
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL	3
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN	3
10		7			Chris Olave			7	Justin Jefferson	WR	MIN	4
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET	5
12		9			Drake London			9	Garrett Wilson	WR	NYJ	6
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET	4
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET	1
15		12			Marvin Harrison			12	Trey McBride	TE	ARI	2
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND	5
17		14						14	Chris Olave	WR	NO	7
18		15						15	Deebo Samuel	WR	SF	8
19								16	Drake London	WR	ATL	9
20								17	A.J. Brown	WR	PHI	10
21								18	Cooper Kupp	WR	LAR	11
22								19	Marvin Harrison	WR	ARI	12
23								20	Malik Nabers	WR	NYG	13
Sheet5

Cell Formulas
Range	Formula
C4:F18	C4	=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($I$4:$I$200)/($J$4:$J$200=C$3)/($L$4:$L$200=$B4),1)),"")

 

[Emerlin]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team	PosRank
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF	1
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL	1
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA	2
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ	2
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL	3
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN	3
10		7			Chris Olave			7	Justin Jefferson	WR	MIN	4
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET	5
12		9			Drake London			9	Garrett Wilson	WR	NYJ	6
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET	4
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET	1
15		12			Marvin Harrison			12	Trey McBride	TE	ARI	2
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND	5
17		14						14	Chris Olave	WR	NO	7
18		15						15	Deebo Samuel	WR	SF	8
19								16	Drake London	WR	ATL	9
20								17	A.J. Brown	WR	PHI	10
21								18	Cooper Kupp	WR	LAR	11
22								19	Marvin Harrison	WR	ARI	12
23								20	Malik Nabers	WR	NYG	13
Sheet5

Cell Formulas
Range	Formula
C4:F18	C4	=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($I$4:$I$200)/($J$4:$J$200=C$3)/($L$4:$L$200=$B4),1)),"")

Thank you for this! It appears to be working well!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Emerlin]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team	PosRank
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF	1
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL	1
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA	2
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ	2
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL	3
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN	3
10		7			Chris Olave			7	Justin Jefferson	WR	MIN	4
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET	5
12		9			Drake London			9	Garrett Wilson	WR	NYJ	6
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET	4
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET	1
15		12			Marvin Harrison			12	Trey McBride	TE	ARI	2
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND	5
17		14						14	Chris Olave	WR	NO	7
18		15						15	Deebo Samuel	WR	SF	8
19								16	Drake London	WR	ATL	9
20								17	A.J. Brown	WR	PHI	10
21								18	Cooper Kupp	WR	LAR	11
22								19	Marvin Harrison	WR	ARI	12
23								20	Malik Nabers	WR	NYG	13
Sheet5

Cell Formulas
Range	Formula
C4:F18	C4	=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($I$4:$I$200)/($J$4:$J$200=C$3)/($L$4:$L$200=$B4),1)),"")

Quick question - If I had the exact same list without the POS Rank column - could a formula be made to do the same?

 

[Peter_SSs]

If I had the exact same list without the POS Rank column - could a formula be made to do the same?

Like this?

24 08 14.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN
10		7			Chris Olave			7	Justin Jefferson	WR	MIN
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET
12		9			Drake London			9	Garrett Wilson	WR	NYJ
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET
15		12			Marvin Harrison			12	Trey McBride	TE	ARI
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND
17		14						14	Chris Olave	WR	NO
18		15						15	Deebo Samuel	WR	SF
19								16	Drake London	WR	ATL
20								17	A.J. Brown	WR	PHI
21								18	Cooper Kupp	WR	LAR
22								19	Marvin Harrison	WR	ARI
23								20	Malik Nabers	WR	NYG
Emerlin

Cell Formulas
Range	Formula
C4:F18	C4	=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($I$4:$I$200)/($J$4:$J$200=C$3),$B4)),"")

 

[Emerlin]

Like this?

24 08 14.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		Cheat Sheet by formual from source data						SOURCE DATA
3		Rank	QB	RB	WR	TE		Rank	Player	POS	Team
4		1		Christian McCaffrey	CeeDee Lamb	Sam LaPorta		1	Christian McCaffrey	RB	SF
5		2		Breece Hall	Tyreek Hill	Trey McBride		2	CeeDee Lamb	WR	DAL
6		3		Bijan Robinson	Ja'Marr Chase			3	Tyreek Hill	WR	MIA
7		4		Jahmyr Gibbs	Justin Jefferson			4	Breece Hall	RB	NYJ
8		5		Jonathan Taylor	Amon-Ra St. Brown			5	Bijan Robinson	RB	ATL
9		6			Garrett Wilson			6	Ja'Marr Chase	WR	CIN
10		7			Chris Olave			7	Justin Jefferson	WR	MIN
11		8			Deebo Samuel			8	Amon-Ra St. Brown	WR	DET
12		9			Drake London			9	Garrett Wilson	WR	NYJ
13		10			A.J. Brown			10	Jahmyr Gibbs	RB	DET
14		11			Cooper Kupp			11	Sam LaPorta	TE	DET
15		12			Marvin Harrison			12	Trey McBride	TE	ARI
16		13			Malik Nabers			13	Jonathan Taylor	RB	IND
17		14						14	Chris Olave	WR	NO
18		15						15	Deebo Samuel	WR	SF
19								16	Drake London	WR	ATL
20								17	A.J. Brown	WR	PHI
21								18	Cooper Kupp	WR	LAR
22								19	Marvin Harrison	WR	ARI
23								20	Malik Nabers	WR	NYG
Emerlin

Cell Formulas
Range	Formula
C4:F18	C4	=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($I$4:$I$200)/($J$4:$J$200=C$3),$B4)),"")

Perfect - many thanks!

 

[Emerlin]

Hi - could someone comment on this please? If it is not possible that is OK - just curious if this is possible. Thanks!

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

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...

www.mrexcel.com