Creating "fair" Playing schedule

[dontbugme1]

We are 9 players to share 2 tennis courts over 26 rounds (this means 56 single-games)
5 players are able to play 12 times and 4 players can play 11 times.
But what is the "best" distribution to get the most different playing pairs.

Thx
Jeff

[Kaper]

Hi,
Of course if this issue appears very often it is worth separate software. If it is just occasional - may be it will be enough to use something readily available. for instance attachment in post #12 from:
http://www.excelforum.com/excel-form...-schedule.html
Enter 9 in upperleft corner, check "Court or field format" and "Double RR" and "use letters"
You will get a set of 72 matches on 4 courts. (I used 20150907 as a key number to generator and received:
(E H) (A C) (B F) (I G)
(F A) (B E) (H C) (G D)
(A G) (D H) (I B) (E F)
(H I) (G B) (D A) (C E)
(D F) (C G) (E I) (A B)
(G E) (F I) (C D) (H A)
(C B) (H F) (A E) (D I)
(I C) (E D) (F G) (B H)
(B D) (I A) (G H) (F C)
(H E) (C A) (F B) (G I)
(A F) (E B) (C H) (D G)
(G A) (H D) (B I) (F E)
(I H) (B G) (A D) (E C)
(F D) (G C) (I E) (B A)
(E G) (I F) (D C) (A H)
(B C) (F H) (E A) (I D)
(C I) (D E) (G F) (H B)
(D B) (A I) (H G) (C F)

Copied to new sheet and after easy splitting each row into 2 you get:
(E H) (A C)
(B F) (I G)
(F A) (B E)
(H C) (G D)
(A G) (D H)
(I B) (E F)
(H I) (G B)
(D A) (C E)
(D F) (C G)
(E I) (A B)
(G E) (F I)
(C D) (H A)
(C B) (H F)
(A E) (D I)
(I C) (E D)
(F G) (B H)
(B D) (I A)
(G H) (F C)
(H E) (C A)
(F B) (G I)
(A F) (E B)
(C H) (D G)
(G A) (H D)
(B I) (F E)
(I H) (B G)
(A D) (E C)

pretty fair schedule with counts:
A 12
B 12
C 11
D 11
E 12
F 11
G 12
H 12
I 11

[Kaper]

PS. I prepared 3 small and easy customized macros to deal with it, and added to original file.
So in sheet "adopted" in column B write names of players. (1)
Go to main sheet (schedule) and generate one round robin schedule in standard format (2)
Transfer data to "adopted" calling macro transfer_to_2_courts (3)
Check column D to see if the distribution is fair (4)
If there are known players who are available for 11 and for 12 times - adjust names location within column B, If everybody can play either 12 or 11 - randomize names sequence calling randomize_names macro (5)
and final step (6) prepare list with names by calling rewrite_with_names macro, so the final result looks like:

Kowalski - Meier Brown - Durand
etc.

The code is really simple:

Please Login or Register  to view this content.

see attached file

[dontbugme1]

Kaper - chapeau!!!
Respect and thank you for your effort!

It works really good, and it is so easy to recreate such plans!

One additional Question: Is it possible to avoid "same encounters/meetings"?

Example:
C vs H and H vs C are the same.
I have attached an adopted version with new columns to count same encounters (Column T and U).
There you can see several meetings white same Players(highlighted in yellow)

adopted tennis plan.xlsm

Again, thank you very much for your effort!
Jeff

[Kaper]

It's impossible. For 9 players number of unique pairs is only 36. See https://en.wikipedia.org/wiki/Combination and the whole list is:

1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 3
2 4
2 5
2 6
2 7
2 8
2 9
3 4
3 5
3 6
3 7
3 8
3 9
4 5
4 6
4 7
4 8
4 9
5 6
5 7
5 8
5 9
6 7
6 8
6 9
7 8
7 9
8 9

To generate it's randomized version use the round robin sheet and uncheck "Double Round Robin" before clicking Generate