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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi & welcome to MrExcel.
Can you please post some sample data from both sheets using the XL2BB add-in?
 
Upvote 0
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))
 
Upvote 0
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))),"")
 
Upvote 0
Can you also post the data from the Players sheet.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top