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

Ottley Kitts

New Member
Joined
Jul 7, 2012
Messages
5
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
Joined
Nov 19, 2010
Messages
708
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
Joined
Jul 7, 2012
Messages
5
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
Joined
Jul 7, 2012
Messages
5
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
Joined
Jul 7, 2012
Messages
5
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
Joined
Nov 1, 2007
Messages
403
</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
1ADJLBCGKEFHI
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
Joined
May 28, 2002
Messages
11,007
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
Joined
Nov 1, 2007
Messages
403
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?
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.
 
Last edited:

Forum statistics

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

Some videos you may like

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...
Top