Golf Trip Pairings with minimal repeats

wentroth

New Member
Joined
Apr 20, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello All, First Time Posting on here

I'm trying to create a golf pairing spreadsheet for a trip of 16 guys playing 6 rounds. Each Round will be 4 tee times of 4 guys. I've used the free pairing generator found on the internet but it's static and doesn't allow for our Ryder Cup Style format. 3 of our 6 rounds will be selected by Team Captains. My goal is to create a spreadsheet that will randomize the pairings so that everyone can play with as many people as possible. I want the ability to re-run this spreadsheet after each round given the captain pairings so that it updates future pairings based on historical pairings. I'm currently trying to use Solver to come up with multiple solutions but it doesn't return anything different. Any Ideas would be greatly appreciated. I've uploaded an image of my spreadsheet as it currently sits (1st image). Currently everyone is playing with everyone. However if you adjust Round 1 to be 1,2,3,......,16 across the board, there are 12 occurrences where players don't get paired (as seen in the 2nd image).

IT department doesn't allow me to download the add-in so I can upload my spreadsheet so I apologize in advance for that.
 

Attachments

  • Golf Pairings Generator.JPG
    Golf Pairings Generator.JPG
    233.2 KB · Views: 1,288
  • Golf Pairings Generator with non matches.JPG
    Golf Pairings Generator with non matches.JPG
    233.3 KB · Views: 1,282

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hello, I may not have understood correctly... but if you take the schedule from the first image and apply a permutation to the numbers 1 to 16 as follows:
1 -> 1
5 -> 2
7 -> 3
14 -> 4
2 -> 5
3 -> 6 etc...
Then you will end up with a schedule with the same pairwise properties, however the 1st row will be 1...16 as requested. Does that help any?
 
Upvote 0
I think I understand what you're saying and you can just adjust the numbers accordingly. But my goal is to be able to run a macro or something that can be done in the middle of the six rounds. For example, if round one's pairings were exactly as it states in the first image, then the second round everyone just randomly played who they wanted to play with, how can I insert the second round pairings and run a macro to determine the remaining 4 rounds to best make everyone play with each other. I'm not sure if I made that clear. My goal is to do a Ryder Cup style selection for a couple rounds and I won't know those until after we've already played a few rounds. So my goal is to create something that accounts for that and adjusts future pairings to try and let everyone play with everyone.
 
Upvote 0
The problem you pose can be solved by combinatorics. There is something called a resolvable balanced incomplete block design (RBIBD) which for 16 players gives a way for all of them to play each other exactly once in 5 rounds of foursomes - for example for players 0 to 15:
{ 0 4 6 7} { 1 2 3 5} { 8 12 14 15} { 9 10 11 13}
{14 11 5 7} { 8 9 2 4} { 6 3 13 15} { 0 1 10 12}
{13 2 12 7} {14 0 9 3} { 5 10 4 15} { 6 8 1 11}
{11 0 2 15} {12 5 6 9} { 3 8 10 7} { 4 13 14 1}
{ 2 6 10 14} { 1 7 9 15} { 3 4 11 12} { 5 0 13 8}

As the schedule above is perfectly balanced with respect to pairs, adding any possible 6th round will lead to a schedule with equivalent properties to your first image - the pairs who play together in the 6th round will be the 2s in the green lower triangular matrix. So taking both my posts together you have a way of finding a schedule for 6 rounds where 2 of the rounds have been fixed before hand. The first fixed round corresponds to 6th round above, and then the whole RBIBD above is adjusted using a permutation to make one of the rounds the same as the 2nd fixed round.

The reason I am suggesting this is that you might struggle to write a macro/algorithm that completes the schedule give the 2 fixed rounds - as these problems tend to be harder that you might think to solve by some optimization strategy.

If you want to fix more than 2 rounds, my advice would be think again, as it is almost certain that it will not be possible to complete the schedule in a way that gives a fair solution.
 
Upvote 0
The problem you pose can be solved by combinatorics. There is something called a resolvable balanced incomplete block design (RBIBD) which for 16 players gives a way for all of them to play each other exactly once in 5 rounds of foursomes - for example for players 0 to 15:
{ 0 4 6 7} { 1 2 3 5} { 8 12 14 15} { 9 10 11 13}
{14 11 5 7} { 8 9 2 4} { 6 3 13 15} { 0 1 10 12}
{13 2 12 7} {14 0 9 3} { 5 10 4 15} { 6 8 1 11}
{11 0 2 15} {12 5 6 9} { 3 8 10 7} { 4 13 14 1}
{ 2 6 10 14} { 1 7 9 15} { 3 4 11 12} { 5 0 13 8}

As the schedule above is perfectly balanced with respect to pairs, adding any possible 6th round will lead to a schedule with equivalent properties to your first image - the pairs who play together in the 6th round will be the 2s in the green lower triangular matrix. So taking both my posts together you have a way of finding a schedule for 6 rounds where 2 of the rounds have been fixed before hand. The first fixed round corresponds to 6th round above, and then the whole RBIBD above is adjusted using a permutation to make one of the rounds the same as the 2nd fixed round.

The reason I am suggesting this is that you might struggle to write a macro/algorithm that completes the schedule give the 2 fixed rounds - as these problems tend to be harder that you might think to solve by some optimization strategy.

If you want to fix more than 2 rounds, my advice would be think again, as it is almost certain that it will not be possible to complete the schedule in a way that gives a fair solution.
Can you help me set up a spreadsheet for 12 players, 6 men per team playing 3 rounds and no one plays with their same team member nor opponent more than once/?
 
Upvote 0
Can you help me set up a spreadsheet for 12 players, 6 men per team playing 3 rounds and no one plays with their same team member nor opponent more than once/?
What you are asking is not mathematically possible. The best advice I could give would be to invite more men. It works for 16 and two teams of 8 and every higher multiple of 4 players. 16 players for example :
(A1 A5 B1 B5) (A4 A8 B4 B6) (A2 A6 B2 B7) (A3 A7 B3 B8)
(A4 A7 B2 B5) (A1 A6 B3 B6) (A3 A8 B1 B7) (A2 A5 B4 B8)
(A2 A8 B3 B5) (A3 A5 B2 B6) (A1 A7 B4 B7) (A4 A6 B1 B8)
(A3 A6 B4 B5) (A2 A7 B1 B6) (A4 A5 B3 B7) (A1 A8 B2 B8)
note that you can divide each team into two ability groups 1-4 & 5-8, then every pairing is mixed ability.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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