Formula to return a sorted list, eliminating values already returned

[shaun32]

Hi all,

I have a giant database of NBA players and their NBA 2k ratings, what I'm trying to do is automatically pull them into team lists:

To get the starting 5 I use this formula in column C: =INDEX(Players!$B:$B,MATCH(MAXIFS(Players!$E:$E,Players!$G:$G,A$1,Players!$F:$F,A2),IF(Players!$G:$G=A$1,IF(Players!$F:$F=A2,Players!$E:$E)),0))
Column A is stagnant for the positions and columns B & D pull through from the Players! sheet based on C

The formula works by looking in the Players! sheet for the highest rated (ratings in $E) player of the starting 5 positions (positions in $F) of the team (team in $G)

Where I'm having trouble is filling out the bench:

I've used this formula to return every player for the Hawks using $G in the Players! sheet again: =IFERROR(INDEX(Players!$B:$B, SMALL(IF((A$1=Players!$G:$G), MATCH(ROW(Players!$G:$G), ROW(Players!$G:$G)), ""),ROWS($A$1:A1))),"")

Is there any way to:

a) return this list sorted by rating in column D (and found in $E in the Players! sheet), and
b) leave out the players already returned in the starting 5 in rows 2-6?

Note: the positions are dynamic in the bench and pull from the Players! sheet

Any help would be much appreciated.

Cheers

 

[Fluff]

Hi & welcome to MrExcel.
Can you please post some sample data from both sheets using the XL2BB add-in?

 

[shaun32]

NBA 2K - Updated Teams.xlsx
	C
3	John Collins
NBA Teams

Cell Formulas
Range	Formula
C3	C3	=INDEX(Players!$B:$B,MATCH(MAXIFS(Players!$E:$E,Players!$G:$G,A$1,Players!$F:$F,A3),IF(Players!$G:$G=A$1,IF(Players!$F:$F=A3,Players!$E:$E)),0))

 

[shaun32]

Whoops lets try that again:

NBA 2K - Updated Teams.xlsx
	A	B	C	D
1	Atlanta Hawks
2	C		Clint Capela	86
3	PF		John Collins	86
4	SF		Danilo Gallinari	84
5	SG		Kevin Huerter	75
6	PG		Trae Young	90
7
8	SF		Bogdan Bogdanovic	79
9	C		Clint Capela	86
10	PF		John Collins	86
11	PG		Kris Dunn	75
12	C		Bruno Fernando	75
13	SF		Danilo Gallinari	84
14	PG		Brandon Goodwin	70
15	SF		Solomon Hill	73
16	SG		Kevin Huerter	75
17	SF		De'Andre Hunter	75
18	PG		Skyler Mays	0
19	PF		Onyeka, Okongwu	0
20	SF		Cam Reddish	73
21	PG		Rajon Rondo	77
22	SF		Tony Snell	72
23	PG		Trae Young	90
NBA Teams

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(Players!$B:$B,MATCH(MAXIFS(Players!$E:$E,Players!$G:$G,A$1,Players!$F:$F,A2),IF(Players!$G:$G=A$1,IF(Players!$F:$F=A2,Players!$E:$E)),0))
B2:B23	B2	=IFERROR(INDEX(Players!#REF!,MATCH(C2, Players!$B:$B,0)),"")
D2:D23	D2	=IFERROR(INDEX(Players!$E:$E,MATCH(C2, Players!$B:$B,0)),"")
A8:A23	A8	=IFERROR(INDEX(Players!$F:$F,MATCH(C8, Players!$B:$B,0)),"")
C8:C23	C8	=IFERROR(INDEX(Players!$B:$B, SMALL(IF((A$1=Players!$G:$G), MATCH(ROW(Players!$G:$G), ROW(Players!$G:$G)), ""),ROWS($A$1:A1))),"")

 

[Fluff]

Can you also post the data from the Players sheet.

 

[shaun32]

and a sample of the raw data:

NBA 2K - Updated Teams.xlsx
	A	B	C	D	E	F	G
1	Name	Full Name	Date of Birth	Age	Rating	Position	Current Team
2	Bogdanovic, Bogdan	Bogdan Bogdanovic	18 August 1992	28	79	SF	Atlanta Hawks
3	Capela, Clint	Clint Capela	18 May 1994	26	86	C	Atlanta Hawks
4	Collins, John	John Collins	23 September 1997	23	86	PF	Atlanta Hawks
5	Dunn, Kris	Kris Dunn	18 March 1994	26	75	PG	Atlanta Hawks
6	Fernando, Bruno	Bruno Fernando	15 August 1998	22	75	C	Atlanta Hawks
7	Gallinari, Danilo	Danilo Gallinari	8 August 1988	32	84	SF	Atlanta Hawks
8	Goodwin, Brandon	Brandon Goodwin	2 October 1995	25	70	PG	Atlanta Hawks
9	Hill, Solomon	Solomon Hill	18 March 1991	29	73	SF	Atlanta Hawks
10	Huerter, Kevin	Kevin Huerter	27 August 1998	22	75	SG	Atlanta Hawks
11	Hunter, De'Andre	De'Andre Hunter	2 December 1997	23	75	SF	Atlanta Hawks
12	Mays, Skyler	Skyler Mays	5 September 1997	23		PG	Atlanta Hawks
13	Okongwu, Onyeka	Onyeka, Okongwu	11 December 2000	19		PF	Atlanta Hawks
14	Reddish, Cam	Cam Reddish	1 September 1999	21	73	SF	Atlanta Hawks
15	Rondo, Rajon	Rajon Rondo	22 February 1986	34	77	PG	Atlanta Hawks
16	Snell, Tony	Tony Snell	10 November 1991	29	72	SF	Atlanta Hawks
17	Young, Trae	Trae Young	19 September 1998	22	90	PG	Atlanta Hawks
Players

Cell Formulas
Range	Formula
D2:D17	D2	=INT(YEARFRAC([@[Date of Birth]],TODAY()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E17	Other Type	Color scale		NO

 

[Fluff]

Many thanks for that, how about

+Fluff v2.xlsm
	A	B	C	D
1	Atlanta Hawks
2	C		Clint Capela	86
3	PF		John Collins	86
4	SF		Danilo Gallinari	84
5	SG		Kevin Huerter	75
6	PG		Trae Young	90
7
8	SF		Bogdan Bogdanovic	79
9	PG		Rajon Rondo	77
10	PG		Kris Dunn	75
11	C		Bruno Fernando	75
12	SF		De'Andre Hunter	75
13	SF		Solomon Hill	73
14	SF		Cam Reddish	73
15	SF		Tony Snell	72
16	PG		Brandon Goodwin	70
17	PG		Skyler Mays	0
18	PF		Onyeka, Okongwu	0
19
Master

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(Players!$B:$B,MATCH(MAXIFS(Players!$E:$E,Players!$G:$G,A$1,Players!$F:$F,A2),IF(Players!$G:$G=A$1,IF(Players!$F:$F=A2,Players!$E:$E)),0))
D2:D7	D2	=IFERROR(INDEX(Players!$E:$E,MATCH(C2, Players!$B:$B,0)),"")
C8:D18	C8	=SORT(FILTER(FILTER(Players!B2:E100,(ISNA(MATCH(Players!B2:B100,C2:C6,0)))*(Players!B2:B100<>"")),(COLUMN(Players!B2:E2)=2)+(COLUMN(Players!B2:E2)=5)),2,-1)
A8:A19	A8	=IFERROR(INDEX(Players!$F:$F,MATCH(C8, Players!$B:$B,0)),"")
Dynamic array formulas.