20 golfers over 3 days playing in foursomes

[golfnut111]

I'm trying to create a spreadsheet where I can 20 golfers playing over 3 days and none of the golfers play with each other twice. On each team will be two "A" players and two "B" players. I've figured out how to make the 1st day random but I can't figure out out to make days 2 & 3 where the players don't play with each other again. Please help! I've been trying for half a day to figure this out. Is there a program to do this?

 

[Perpa]

Golfnut111,
See if the link below by Stephen Crump is any help:
Perpa


"Common Social Golfer Problem"
https://www.mrexcel.com/forum/excel...lfer-into-teams-4-no-repeats-over-3-days.html

 

[golfnut111]

Thanks Perpa. I did see that, but I still have problems when I was working with only 20 golfers.

 

[FracinDean]

I think I have an idea that may work for you. Go back to the "Common Social Golfer Problem" thread. Their is another link at the bottom of that thread. The other link takes you to a free tool that I think will do this for you very well - but there is a twist. Instead of putting in 4 as the number of golfers per team, use 2 golfers, and use 10 golfers instead of 20. Then you generate an A golfer schedule and a B golfer schedule, and then just put them together.

 

[golfnut1]

That is a good idea. I’ll give it a try. Thanks again.

 

[Michael M]

@golfnut1
The link provided does allow for 20 golfers over 3 events !
Simply click on the pairings generator button and it will take you to the build schedule where you can set it up for your field.

 

[golfnut111]

I'm still having problems solving this. The problem is I have 10 better players in one group or the "A" players and 10 worse players in another group or "B" players. I want 2 A and 2 B players in each foursome, but I don't want anyone to play with the same players twice over the 3 days. Maybe there isn't a simple way to do this and I have to just fumble with the different names until I get something that works. Thanks for all you help though. I really appreciate it.

 

[Perpa]

golfnut11,
See if this layout is any help...


Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#BFBFBF][/td][td]Tee 1[/td][td][/td][td]Tee 2[/td][td][/td][td]Tee 3[/td][td][/td][td]Tee 4[/td][td][/td][td]Tee 5[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Day 1[/td][td]2A 4A 2B 4B[/td][td][/td][td]1A 5A 1B 5B[/td][td][/td][td]7A 8A 7B 8B[/td][td][/td][td]3A 10A 3B 10B[/td][td][/td][td]6A 9A 6B 9B[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Day 2[/td][td]2A 4A 7B 8B[/td][td][/td][td]3A 10A 2B 4B [/td][td][/td][td]1A 5A 3B 10B[/td][td][/td][td]6A 9A 1B 5B [/td][td][/td][td]7A 8A 6B 9B[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Day 3[/td][td]2A 4A 6B 9B[/td][td][/td][td]1A 5A 7B 8B[/td][td][/td][td]3A 10A 1B 5B [/td][td][/td][td]6A 9A 2B 4B[/td][td][/td][td]7A 8A 3B 10B[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]20 Golfers - 4somes - 3 Days[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]A' Flight[/td][td][/td][td][/td][td][/td][td]'B' Flight[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]1A[/td][td]Alan[/td][td][/td][td][/td][td]1B[/td][td]Peter[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]2A[/td][td]George[/td][td][/td][td][/td][td]2B[/td][td]Frank[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]3A[/td][td]Jim[/td][td][/td][td][/td][td]3B[/td][td]Jeff[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]4A[/td][td]Pat[/td][td][/td][td][/td][td]4B[/td][td]Sam[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]5A[/td][td]Dave[/td][td][/td][td][/td][td]5B[/td][td]Conrad[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]6A[/td][td]Ray[/td][td][/td][td][/td][td]6B[/td][td]Terry[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]7A[/td][td]Miles[/td][td][/td][td][/td][td]7B[/td][td]Bob[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]8A[/td][td]Larry[/td][td][/td][td][/td][td]8B[/td][td]Ted[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]9A[/td][td]Duncan[/td][td][/td][td][/td][td]9B[/td][td]Jerry[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]10A[/td][td]Murphy[/td][td][/td][td][/td][td]10B[/td][td]Ziggy[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]




I used the golf program in the last post of the "Common Social Golfer Problem" thread mentioned previously.
I had 10 'A' players (1A, 2A, and etc) and 10 'B' players (2A, 2B, and etc.) in the following format.
Then I used the code below to replace the corresponding name for each player in each 4 some.
I hope this is helpful.
Perpa

Sub AddPlayerNames()
Dim myString1, myString2 As String
Dim rw As Long


For rw = 12 To 21
      
    myString1 = Cells(rw, "A")
    myString2 = Cells(rw, "B")
    Range("B2:J6").Replace What:=myString1, Replacement:=myString2, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
Next


For rw = 12 To 21
      
    myString1 = Cells(rw, "E")
    myString2 = Cells(rw, "F")
    Range("B2:J6").Replace What:=myString1, Replacement:=myString2, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
Next
End Sub

 

[golfnut111]

If I'm reading this correctly, 2A & 4A are playing together all 3 days. What I want is to each player to have 9 different partners over the 3 days. He wouldn't see the same partner over the 3 days. I keep trying, but I'm thinking there might not be a solution.

 

[Perpa]

golfnut111,
This is about as close as I was able to get...the last 2 foursomes (in red font) have played other players more than once, but 13 out of 15 foursomes seem well mixed...
Perpa

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][th]

Q​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

53​

[/td][td][/td][td] Tee1[/td][td] Tee2[/td][td] Tee 3[/td][td] Tee 4[/td][td] Tee 5[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

54​

[/td][td]Day 1[/td][td]1A 2A 1B 2B[/td][td]3A 4A 3B 4B[/td][td]5A 6A 5B 6B[/td][td]7A 8A 7B 8B[/td][td]9A 10A 9B 10B [/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

55​

[/td][td]Day 2[/td][td]10A 1A 4B 8B[/td][td]2A 9A 3B 6B[/td][td]4A 8A 5B 7B[/td][td]3A 6A 10B 1B[/td][td]5A7A 2B 9B[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

56​

[/td][td]Day 3[/td][td]1A 9A 3B 7B[/td][td]6A 8A 2B 4B [/td][td]2A 4A 5B 10B[/td][td]5A 10A 6B 8B [/td][td]3A 7A 1B 9B[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]