Golf Trip Pairings w/ No Repeats

pstoller

New Member
Joined
Mar 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
We have a 32-man golf trip that is split up into 2 teams of 16. We play 4 rounds. I am trying to come up with a schedule where everyone has a different partner all 4 rounds, and everyone will play against different people on the opposing team over the 4 rounds (8 in total), with no overlap or repeats. If it's even possible I figure there has to be a formula but for the life of me can't figure it out. Any help would be greatly appreciated.
 

Attachments

  • Golf Pairings.jpg
    Golf Pairings.jpg
    44 KB · Views: 854

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
If everything can be truly random. I'd assign each player a RAND() number and convert to value. Sort each team by that number--low-to-high. The lowest of each team is Player 1. The highest is Player 16. Then I'd shift the players manually by one slot for each round.
Rd 1: 1 vs 1, 2 vs 2, 3 vs 3, 4 vs 4, etc...
Rd 2: 1 vs 16, 2 vs 1, 3 vs 2, 4 vs 3, etc...
Rd 3: 1 vs 15, 2 vs 16, 3 vs 1, 4 vs 2, etc...
Rd 4: 1 vs 14, 2 vs 15, 3 vs 16, 4 vs 1, etc...

If shifting by one slot is too predictable I might have Excel select another random number between 1 and 15 to determine the shift.

It's not sophisticated, but it's not difficult, either.
 
Upvote 0
Attempt #2 at VBA solution.

Team Rank Golf VBA.xlsm
EFGHIJKL
1Round 1Round 2Round 3Round 4
2EuropeUSAEuropeUSAEuropeUSAEuropeUSA
3171514113
425212210214
53237316313
64846415414
7565854515
8626668616
9777476711
1081081814813
11959792914
121041061010101
13111611121111112
141212124125123
151351311132134
16141214131415147
17151515111571512
18164161516121610
Sheet7


VBA Code:
Sub Golf()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim Teams As Integer:       Teams = 16
Dim AA() As Object:         ReDim AA(1 To Teams)
Dim AL As Object:           Set AL = CreateObject("System.Collections.ArrayList")
Dim r As Range:             Set r = Range("E3:E18")
Dim SP() As String
Dim v As Variant

For k = 1 To UBound(AA)
    Set AA(k) = CreateObject("System.Collections.ArrayList")
Next k

For i = 0 To Teams - 1
    For j = 0 To Teams - 1
        v = i + 1
        AA(v).Add Join(Array(v, ((j) Mod Teams) + 1), ",")
    Next j
Next i

For col = 1 To 4
    For ro = 1 To Teams
        Randomize
        ri = Int(AA(ro).Count * Rnd())
        SP = Split(AA(ro).Item(ri), ",")
        AL.Add AA(ro).Item(ri)
        AA(ro).removeAt (ri)
        AA(SP(1)).Remove (SP(1) & "," & SP(0))
    Next ro
    With r
        .Value = Application.Transpose(AL.toArray)
        .TextToColumns DataType:=xlDelimited, Comma:=True
    End With
    Set r = r.Offset(, 2)
    AL.Clear
Next col

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
 
Upvote 0
The above post was closer, but I'm seeing an issue with it again. :rolleyes:
 
Upvote 0
So the problem here, aside from the duplicate numbers in the USA column, but also having the Europe team be 1-16 for each round, assuming every 2 rows they would be grouped as teammates for that match then Europe would have the same partner all 4 rounds.
 
Upvote 0
I got it. I couldn't figure out an Excel formula, ended up just manually moving things around until everything checked out. Thanks for the help and suggestions.
 

Attachments

  • Picture1.png
    Picture1.png
    96 KB · Views: 113
Upvote 0
Solution

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top