I'm stuck !! please help

[markads]

I am trying to create a match schedule consisting of 8 players playing 7 matches in teams of two. I want an even schedule where each player (1) plays with each of the other players only once and (2) plays against each of the other players a consistent number of times.

I have a pairing schedule that solves item (1) but the game schedule means that some players play against certain other players 3 times and against others players 0 times. I have created a manual matrix to show this but it took some time.

I need to mix up the game schedule to make the number of times players meet more consistent. Re drawing the matrix to count the opponents is however very time consuming - is there a way excel can automate this ??

I have tried using COUNTIFS with two ranges - Team A & Team B as coloured in the attached file. This only seems to work where a player's name appears in the 1st column of each range. There must be a way but I am not smart enough any help would be appreciated.

 

[NARAYANK991]

Hi ,

I doubt that Excel can help in such optimization problems , unless we can frame the logic in terms that Excel can understand ; I am not sure that I can do it.

Secondly , I am not clear on how matches you want in the schedule , is it just 7 or is it 28 ?

If it is only 7 , can you check whether the following schedule is better ?

A & D --------- vs. --------- B & C

A & C --------- vs. --------- F & G

A & B --------- vs. --------- E & H

D & E --------- vs. --------- F & H

C & G --------- vs. --------- B & E

D & F --------- vs. --------- E & G

B & F --------- vs. --------- G & H

Narayan