SanFelippo
Board Regular
- Joined
- Apr 4, 2017
- Messages
- 124
Hello,
So I have this list of information which you can see below:
In this chart I have a list of players (A - S), what their respective strength is, and the time they are able to cover in the time range 0 - 24.
I am trying to figure out if there is a way to get excel (whether through a macro or regular formula) to run through the entire list of players and optimally match up Offensive Players with Defensive Players while at the same time maximizing (getting as close as possible to) covering the entire 24 hour time range.
For example, as you can see in the picture, "Player B" and "Player I" would be a perfect match, as one is Offensive, one is Defensive, and together they would cover the entire 24 hour range. Naturally, once two players have been put together, they would no longer be available as matches for the rest of the list.
I could do this manually, but at some point I could have a list of over 40 or 50 players, so it would get extremely difficult.
Does anyone know of a way to do this?
So I have this list of information which you can see below:
In this chart I have a list of players (A - S), what their respective strength is, and the time they are able to cover in the time range 0 - 24.
I am trying to figure out if there is a way to get excel (whether through a macro or regular formula) to run through the entire list of players and optimally match up Offensive Players with Defensive Players while at the same time maximizing (getting as close as possible to) covering the entire 24 hour time range.
For example, as you can see in the picture, "Player B" and "Player I" would be a perfect match, as one is Offensive, one is Defensive, and together they would cover the entire 24 hour range. Naturally, once two players have been put together, they would no longer be available as matches for the rest of the list.
I could do this manually, but at some point I could have a list of over 40 or 50 players, so it would get extremely difficult.
Does anyone know of a way to do this?
PHP:
Current Formulas:
F3 = REPT(LEFT($C3), IF($E3 < $D3, OR(F$2<=$E3-1, F$2>=$D3), AND(F$2>=$D3, F$2<=$E3-1)))
F23 = COUNTIF(F$3:F$21,"O")
F24 = COUNTIF(F$3:F$21,"D")