Create a schedule for golf - 12 players, 3 x 4 each day over 5 days. Minimum number of repeat players

[Ottley Kitts]

I'm trying to create a 5 day golfing schedule for 12 players - this means there are 60 cells to fill. I want everyone to play with each other once but then have the minimum of repeats. I've tried the MRAND with INDEX which is great for each day's play but then find there are a lot of repeat groups on other days. Any advice VERY welcome. I'm using A through to L to represent the players names.

Using this formula: =INDEX($A$2:$A$13,MRAND()+1,,TRUE)

To try to explain this graphically I have used the MRAND to randomly generate my players each day so I use all 12 players and then copied it across to the other remaining days. This works to an extent but if you scroll down you will then see, just taking Player A as an example, he plays with the two same players three times (H & I), yet he doesn't player with one player (K) at all.

PLAYERS Wed Thu Fri Sat Sun A L K G H A B E E I E E C J H A L I D K C H A D E F A B C C F G J L G L G D I D F B H I F E I G I H D K J H J C L F B J K A B J K F L B G C D K Player: A A A A A H J I H E C I G E I B F H L D Played with: H 3 times H 3 times I 3 times E 2 times Not Played: K <tbody> </tbody>

<tbody>
</tbody>

Any suggestions would be greatly appreciated. Many thanks Ottley

<tbody>
</tbody>

 

[sezuh]

Hi Ottley,
try this link or the code below which i got it from this wonderful forum.
I hope it would help you .Actually i was going to start a new tread regarding this code?
http://www.devenezia.com/round-robin/forum/YaBB.pl?board=requests/20

Sub all_play_all()
Dim c() As Variant, u() As Variant, flg As Byte
Dim i As Long, j As Long, n As Long
n = 12
If n Mod 2 = 1 Then n = n + 1: flg = 1
ReDim c(1 To n, 1 To n / 2 + 1), u(1 To n)
For i = 1 To n - 1
If flg = 1 Then u(n) = "bye" Else u(n) = n
For j = 1 To n - 1
    u(j) = i + j - 1
    If u(j) > n - 1 Then u(j) = u(j) - n + 1
Next j
For j = 1 To n / 2
    c(i, j + 1) = u(j) & "  " & u(n - j + 1)
Next j
c(i, 1) = "DAY " & i
Next i
Range("A1").Resize(n, n / 2 + 1) = c
End Sub

Sorry guys i dont know who's code it is?

 

[shg]

That's a round-robin, sezuh.

See http://www.cs.brown.edu/~sello/solutions.html

 

[Ottley Kitts]

Many thanks for taking a look Sezuh - unfortunately it doesn't give me the results I need. Tried your code but taking the results for just the first 5 days I have the following results. Each player must play all other 11 golfers at least once. They play in groups of 4 (if you are a golfer, known as a 4-ball) so there are 3 groups of 4 golfers (i.e. 12 golfers play) each day for 5 days.

Played with once - Players 3, 5, 7, 9 and 10
Played with twice - Players 6 and 12
Played with three times - Players 2 and 11

NEVER played with - 4 and 8

Thanks again
Ottley

 

[sezuh]

Hi Ottley,
Try this link please.
http://www.devenezia.com/round-robin/forum/YaBB.pl?num=1233635660
And also try SHG suggestion aswell.

 

[Ottley Kitts]

Many thanks for the schedules - I will certainly bear these in mind if our format changes but this time round I don't think it will help as we have 3 groups of 4 golfers over 5 days which I don't think is included. The nearest being '5 weeks play in 4 groups of 4 golfers'. Appreciate your help. Kind regards.
Ottley

 

[Ottley Kitts]

Many thanks Sezuh - the link to the golfer schedule by Ian Wakeling certainly looks promising. Buddy pairs unfortunately wouldn't be an option for our format but I will definitely check out in detail the other '12 men 5 rounds' option. Really appreciate your help with this - I was beginning to go round in circles with this - spending hours just to stand still! Thanks again.

 

[JackBean]

</SPAN>This problem with 5 Rows brings up the question of how it can be determined when all of the 0 Pairings can be eliminated from the Pairing Grid,
for example all 1's and 2's.
According to the site in the previous post, with 5 Rows these can not be eliminated.

The following solutions all have the same STDEV (0.647750275631296) of the pairing grid,
(all 1's and 2's except for):
6 pairings of 0
5 pairings of 0 and 1 pairing of 3 (Not shown)
4 pairings of 0 and 2 pairings of 3 (Not shown)

So far this is also my best result, 6 pairings of 0:

Excel Workbook
	A	B	C
1	ADJL	BCGK	EFHI
2	AFGI	BEKL	CDHJ
3	AGHK	BDFL	CEIJ
4	ACEL	BDGI	FHJK
5	ABIJ	CDFK	EGHL
...

Pairing Grid:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		A	B	C	D	E	F	G	H	I	J	K	L
2	A	X	X	X	X	X	X	X	X	X	X	X	X
3	B	2	X	X	X	X	X	X	X	X	X	X	X
4	C	1	1	X	X	X	X	X	X	X	X	X	X
5	D	2	1	1	X	X	X	X	X	X	X	X	X
6	E	0	1	1	1	X	X	X	X	X	X	X	X
7	F	1	2	1	2	1	X	X	X	X	X	X	X
8	G	2	1	2	0	1	2	X	X	X	X	X	X
9	H	1	1	2	2	2	2	2	X	X	X	X	X
10	I	1	2	2	1	2	2	0	1	X	X	X	X
11	J	2	1	1	2	2	0	1	2	1	X	X	X
12	K	2	1	1	2	2	1	2	0	2	1	X	X
13	L	1	2	2	1	2	1	2	0	1	2	1	X
...

</SPAN>

 

[tusharm]

If you haven't already resolved the issue...

**** Kusleika is an avid golfer and AFAIK has addressed this issue at www.dailydoseofexcel.com. You may want to see if searching that site is of any value to you.

I'm trying to create a 5 day golfing schedule for 12 players - this means there are 60 cells to fill. I want everyone to play with each other once but then have the minimum of repeats. I've tried the MRAND with INDEX which is great for each day's play but then find there are a lot of repeat groups on other days. Any advice VERY welcome. I'm using A through to L to represent the players names.

Using this formula: =INDEX($A$2:$A$13,MRAND()+1,,TRUE)

To try to explain this graphically I have used the MRAND to randomly generate my players each day so I use all 12 players and then copied it across to the other remaining days. This works to an extent but if you scroll down you will then see, just taking Player A as an example, he plays with the two same players three times (H & I), yet he doesn't player with one player (K) at all.

PLAYERS Wed Thu Fri Sat Sun A L K G H A B E E I E E C J H A L I D K C H A D E F A B C C F G J L G L G D I D F B H I F E I G I H D K J H J C L F B J K A B J K F L B G C D K Player: A A A A A H J I H E C I G E I B F H L D Played with: H 3 times H 3 times I 3 times E 2 times Not Played: K <tbody> </tbody>

<tbody>
</tbody>

Any suggestions would be greatly appreciated. Many thanks Ottley

<tbody>
</tbody>

 

[JackBean]

The question was brought up in Post 8 whether the 0's in the Pairing Grid can be eliminated.
Here is an example showing no 0's (all 1,2 and one 5):

Excel Workbook
	A	B	C
1	AEFL	BGHJ	CDIK
2	ACEH	BFIJ	DGKL
3	ABDJ	CEFG	HIKL
4	ACGI	BEJK	DFHL
5	AFGK	BCJL	DEHI
...

Pairing Grid:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		A	B	C	D	E	F	G	H	I	J	K	L
2	A	X	X	X	X	X	X	X	X	X	X	X	X
3	B	1	X	X	X	X	X	X	X	X	X	X	X
4	C	2	1	X	X	X	X	X	X	X	X	X	X
5	D	1	1	1	X	X	X	X	X	X	X	X	X
6	E	2	1	2	1	X	X	X	X	X	X	X	X
7	F	2	1	1	1	2	X	X	X	X	X	X	X
8	G	2	1	2	1	1	2	X	X	X	X	X	X
9	H	1	1	1	2	2	1	1	X	X	X	X	X
10	I	1	1	2	2	1	1	1	2	X	X	X	X
11	J	1	5	1	1	1	1	1	1	1	X	X	X
12	K	1	1	1	2	1	1	2	1	2	1	X	X
13	L	1	1	1	2	1	2	1	2	1	1	2	X

<TBODY> </TBODY>

The number of possible Rows of 3 Groups of 4 from 12 Characters,
where each Character appears one, is 5775.

For how this is calculated see:
Find and complete the series of numbers?
http://www.mrexcel.com/forum/showthread.php?556229-Find-and-complete-the-series-of-numbers

There are COMBIN(5775,5)=53,435,081,520,976,200 possible combinations of 5 Rows from these 5775 Rows.

Both of the solutions found so far with no 0's have a 5.