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

#### Ottley Kitts

##### New Member
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

##### Well-known Member
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
Code:
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?

#### Ottley Kitts

##### New Member
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

#### Ottley Kitts

##### New Member
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

##### New Member
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

##### Active Member
</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
ABC
2AFGIBEKLCDHJ
3AGHKBDFLCEIJ
4ACELBDGIFHJK
5ABIJCDFKEGHL
 ...

Pairing Grid:
Excel Workbook
ABCDEFGHIJKLM
1ABCDEFGHIJKL
2AXXXXXXXXXXXX
3B2XXXXXXXXXXX
4C11XXXXXXXXXX
5D211XXXXXXXXX
6E0111XXXXXXXX
7F12121XXXXXXX
8G212012XXXXXX
9H1122222XXXXX
10I12212201XXXX
11J211220121XXX
12K2112212021XX
13L12212120121X
 ...
</SPAN>

#### tusharm

##### MrExcel MVP
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

##### Active Member
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
ABC
1AEFLBGHJCDIK
2ACEHBFIJDGKL
3ABDJCEFGHIKL
4ACGIBEJKDFHL
5AFGKBCJLDEHI
 ...

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?

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.

Last edited:

1,081,513
Messages
5,359,225
Members
400,521
Latest member
smarty1995

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...