Golf Trip Pairings with minimal repeats

[wentroth]

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.

 

[Graymalkin]

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?

 

[wentroth]

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.

 

[Graymalkin]

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.

 

[MARK SIMOWITZ]

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/?

 

[AhoyNC]

This isn't Excel, but a free website to set pairings without repeats.

Free round robin tournament schedule / pairings generator – GolfSoftware.com

Use our free round robin tournament generator to create your single round robin, double round robin or play with all schedule for your tournaments and leagues.

golfsoftware.com

 

[Graymalkin]

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.