Create List of All Possible Lineup Combinations Given Positional Constraints

namebrick

New Member
Joined
Apr 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I want to find all possible lineup combinations for a list of players with one at each position. However, the positions and player eligibilities aren't mutually exclusive.

In the below table I assigned player IDs (A-T) and position IDs (1-9), and made a table off of their eligibilities where 1 means they are eligible and 0 means they are not.

PlayerIDEligiblilityC (1)1B (2)2B (3)3B (4)SS (5)MI (6)CI (7)OF (8)UT (9)
Willson ContrerasAC100000001
Cody BellingerB1B,OF010000111
Mike MoustakasC2B,3B001101101
Rafael DeversD3B000100101
Javier BaezESS000011001
Rhys HoskinsF1B010000101
Fernando Tatis Jr.GSS000011001
Kris BryantH3B,OF000100111
Yordan AlvarezIOF000000011
Starling MarteJOF000000011
J.D. MartinezKOF000000011
Victor RoblesLOF000000011
Bo BichetteMSS000011001
Joey VottoN1B010000101
Nick SolakO2B,3B001101101
Carlos CorreaPSS000011001
Andrelton SimmonsQSS000011001
Bryan ReynoldsROF000000011
Nick SenzelSOF000000011
Nick MadrigalT2B,SS001011001

For a "lineup", I need the following quantity at each position. Note that MI is a combination of 2B + SS, CI is 1B + 3B, and UT is any player.

Position IDPositionQuantity Needed
1C1
21B1
32B1
43B1
5SS1
6MI1
7CI1
8OF5
9UT1

I want to find a way to compute all possible lineup combinations for positions 1-9 without duplicating (e.g. if player B is at 1B he cannot also play CI) . The order of the players does not matter, for instance if player B and F are both in the lineup, I don't care which is at 1B and which is at CI. I just want a list of all possible 13-player combinations.

What will the output look like? A string of player ID text showing letters of those in the lineup? (e.g. "ABCDEGFHIJKLM")

The end goal is to use projections to 1) optimize the lineup by points and 2) find out which positions need more options in order to optimize points, but this is how I envision the first step playing out.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Another way of listing player IDs at each position is shown below.

PositionPosIDPlayer ID Possibilities
C1A
1B2BFN
2B3COT
3B4CDHO
SS5EGMPQT
MI6CEGMOPQT
CI7BCDFHNO
OF8BHIJKLRS
UT9ABCDEFGHIJKLMNOPQRST
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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