Formula to return a sorted list, eliminating values already returned

shaun32

New Member
Joined
Dec 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

1607056432716.png


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:

1607056695215.png


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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Can you please post some sample data from both sheets using the XL2BB add-in?
 

shaun32

New Member
Joined
Dec 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
NBA 2K - Updated Teams.xlsx
C
3John Collins
NBA Teams
Cell Formulas
RangeFormula
C3C3=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

New Member
Joined
Dec 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Whoops lets try that again:
Cell Formulas
RangeFormula
C2:C6C2=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:B23B2=IFERROR(INDEX(Players!#REF!,MATCH(C2, Players!$B:$B,0)),"")
D2:D23D2=IFERROR(INDEX(Players!$E:$E,MATCH(C2, Players!$B:$B,0)),"")
A8:A23A8=IFERROR(INDEX(Players!$F:$F,MATCH(C8, Players!$B:$B,0)),"")
C8:C23C8=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you also post the data from the Players sheet.
 

shaun32

New Member
Joined
Dec 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
and a sample of the raw data:
NBA 2K - Updated Teams.xlsx
ABCDEFG
1NameFull NameDate of BirthAgeRatingPositionCurrent Team
2Bogdanovic, BogdanBogdan Bogdanovic18 August 19922879SFAtlanta Hawks
3Capela, ClintClint Capela18 May 19942686CAtlanta Hawks
4Collins, JohnJohn Collins23 September 19972386PFAtlanta Hawks
5Dunn, KrisKris Dunn18 March 19942675PGAtlanta Hawks
6Fernando, BrunoBruno Fernando15 August 19982275CAtlanta Hawks
7Gallinari, DaniloDanilo Gallinari8 August 19883284SFAtlanta Hawks
8Goodwin, BrandonBrandon Goodwin2 October 19952570PGAtlanta Hawks
9Hill, SolomonSolomon Hill18 March 19912973SFAtlanta Hawks
10Huerter, KevinKevin Huerter27 August 19982275SGAtlanta Hawks
11Hunter, De'AndreDe'Andre Hunter2 December 19972375SFAtlanta Hawks
12Mays, SkylerSkyler Mays5 September 199723PGAtlanta Hawks
13Okongwu, OnyekaOnyeka, Okongwu11 December 200019PFAtlanta Hawks
14Reddish, CamCam Reddish1 September 19992173SFAtlanta Hawks
15Rondo, RajonRajon Rondo22 February 19863477PGAtlanta Hawks
16Snell, TonyTony Snell10 November 19912972SFAtlanta Hawks
17Young, TraeTrae Young19 September 19982290PGAtlanta Hawks
Players
Cell Formulas
RangeFormula
D2:D17D2=INT(YEARFRAC([@[Date of Birth]],TODAY()))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E17Other TypeColor scaleNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Many thanks for that, how about
+Fluff v2.xlsm
ABCD
1Atlanta Hawks
2CClint Capela86
3PFJohn Collins86
4SFDanilo Gallinari84
5SGKevin Huerter75
6PGTrae Young90
7 
8SFBogdan Bogdanovic79
9PGRajon Rondo77
10PGKris Dunn75
11CBruno Fernando75
12SFDe'Andre Hunter75
13SFSolomon Hill73
14SFCam Reddish73
15SFTony Snell72
16PGBrandon Goodwin70
17PGSkyler Mays0
18PFOnyeka, Okongwu0
19 
Master
Cell Formulas
RangeFormula
C2:C6C2=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:D7D2=IFERROR(INDEX(Players!$E:$E,MATCH(C2, Players!$B:$B,0)),"")
C8:D18C8=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:A19A8=IFERROR(INDEX(Players!$F:$F,MATCH(C8, Players!$B:$B,0)),"")
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top