Excel Coding to Pair Two Items Together While Maximizing Time Covered

[SanFelippo]

Hello Friends,

This is a bit of a tricky one to do in excel, as it involves some Operations Research (if anyone has ever taken that class in college).

I have a list of People or "Items" if you will. In the next column I have their strengths, let's say they are either Offensive or Defensive. Then in the next two columns, I have the times that they are available (it runs from 0 - 24, so someone could be available from say 7 - 19, or someone could be available from 20 - 6, or 6 - 13, etc).

I have included a table below to show what I mean.

Anyway, the idea is this. Each Person on this list has their own "account." Ideally I want each account to have the full scale of time ranges covered (0 - 24). This involves having people merge accounts, but I would want to make sure that the two (or three people if needed) who merge their accounts have at least 1 person who's strength is "Offensive" and one who's strength is "Defensive."


Example:

So if I were to have an "Offensive" Player who was available from 7 - 19, I would want to pair them with one or two other people of whom at least one would be "Defensive" who would be available for the missing time of 20 - 6. Say I have a "Defensive" player who is available from 20 - 6. They would then merge their accounts, and both play on the same account, one covering the time range from 7 - 19, and the other would cover the time range from 20 - 6.

It is going to be impossible probably to get every account to be covered for the full 24 hours, but I would like to get as close as possible. I would say the limit of players on one account is 3 total. Overlap of times is okay as well, so if you had two players, one who played 4 - 16 and one who played 13 - 5 who were Offensive and Defensive respectively, it would be okay to pair them together.

Below would be an example of what my data would look like:

Player	Strength	Time Start	Time Finished
A	Offensive	0	18
B	Offensive	5	20
C	Defensive	4	17
D	Defensive	23	9
E	Defensive	19	10
F	Offensive	16	4
G	Defensive	12	24
H	Offensive	23	12
etc.	etc.	etc.	etc.

<tbody>
</tbody>

I don't know if it is exactly possible to write code to do this (either in regular excel using formulas or with VBA). If this doesn't seem like something that can be done, or it is something one of you knows can't be done, just go ahead and say that haha.

I don't need this to actually go ahead and merge the accounts, I would just like it to maybe show in the next column or two the potential matches each person would have.

 

[thisoldman]

Would a chart help?

Book1
	B	C	D	E	F	G	H	I	J	K
2	Player	Strength	Start	End	0	1	2	3	4	5
3	A	Offensive	0	18	O	O	O	O	O	O
4	B	Offensive	5	20						O
5	C	Defensive	4	17					D	D
6	D	Defensive	23	9	D	D	D	D	D	D
7	E	Defensive	19	10	D	D	D	D	D	D
8	F	Offensive	16	4	O	O	O	O	O
9	G	Defensive	12	24
10	H	Offensive	23	12	O	O	O	O	O	O
11
12				Offensive Count	3	3	3	3	3	3
13				Defensive Count	2	2	2	2	3	3
Sheet1

Cell Formulas
Range	Formula
F3	=REPT(LEFT($C3), IF($E3<$D3, OR(F$2<=$E3, F$2>=$D3), AND(F$2>=$D3, F$2<=$E3)))
F12	=COUNTIF(F$3:F$10, "O")
F13	=COUNTIF(F$3:F$10, "D")

 

[SanFelippo]

That's actually a very good way to set it up to start with. So now the idea is to have excel figure out which players can be paired together to try and make sure the entire 0-24 if covered as much as possible. Like in the example, you see that Player A is missing 19-24 and he is an Offensive Player. A good match for him would be Player G or E, as they are Defensive, and their times would ensure that the entire 0-24 is covered. However, once it is decided which player to pair Player A with, that player can no longer be paired with anyone else unless you want to try and merge 3 accounts.

This is where we get into trying to get excel to make sure all 24 hours are covered by pairing people up, but also doing it optimally, AKA as many pairings cover as much of the 24 hours as possible.

Does that make sense what I am trying to get excel to do? I am not sure if it can be done honestly. I don't know if Excel can be programmed to try and optimize pairing things.

 

[thisoldman]

First, I want to correct the mistake I made: the chart shows 25 hours. The last hour it should show is 23. So delete the last column.

The first hour is from 0 to 1, the last hour should be 23. If Person A is available from 0 to 18, he or she cannot cover the hour between 18 and 19: hour 18 should be blank. The formula in F2 should be changed to:

=REPT(LEFT($C3), IF($E3 < $D3, OR(F$2<=$E3-1, F$2>=$D3), AND(F$2>=$D3, F$2<=$E3-1)))

Solving the pairs problem formulaically, either in a worksheet or with VBA is beyond me. I included the counts of Offensive and Defensive at the bottom and highlighted the hours where only one of the two categories is available.

You could use this chart and call it your Availability chart. Then use a second copy for a Schedule chart. Enter trial scheduled start and end times. If you have a zero in either of the two Count rows, your schedule needs to be adjusted.

 

[thisoldman]

Thinking about this.

Compare the pairing of {A, E} with the pair {A, G}. More hours are covered with {A, G}—the sum of the counts of ones and twos is closer to 24 than with {A, E}.

For a first cut, pair each Offensive with every Defensive player. Rank all the combinations by the combined counts of ones and twos, greater is better. I am unsure how to proceed from there, but you now know the strongest couples. The addition of a possible third player to a team...

 

[SanFelippo]

This a great start. Thanks a ton man. I am going to play around with this to see if I can figure something out. I will repost in a new topic this question with your ideas implemented to see if someone else has any ideas.

Again, thanks!!