Tournament Schedule - Golf pairing

[JT123]

We have 12 players, divided into 2 teams of 6, playing 6 rounds of golf. I want each player to play at least once with every player in their own team. In every fourball, there should be 2 players from each team. The matchups against the opposing team should be as evenly distributed as possible.

Can someone please help me with this?

 

[Eric W]

Welcome to the MrExcel forum!
We get a lot of these kind of golf pairing questions. Every one is a little different. And as you've found out, these questions are usually really hard! So I ended up just writing a program that randomly set up the pairings, calculated how good it was, and repeat until I found something decent. I ran over 100,000 iterations, and I actually got a result better than I expected. Here it is:

Book1
	L	M	N	O	P	Q	R
1	Team 1	Team 2		Round	Foursome 1	Foursome 2	Foursome 3
2	A	U		1	DFWY	ACXZ	BEUV
3	B	V		2	EFUW	BDYZ	ACVX
4	C	W		3	DEUX	ABYZ	CFVW
5	D	X		4	AFWZ	CEVY	BDUX
6	E	Y		5	BFWX	CDVY	AEUZ
7	F	Z		6	ADXY	BCUW	EFVZ
Sheet2

This works quite well. Every player on team 1 plays with every other player on team 1 at least once. Every player on team 2 plays with every player on team 2 at least once, except for U and Y. Moreover, every player on team one plays with every player on team 2 at least once, and vice versa.

Hope this helps!

 

[Eric W]

I ran a few more iterations, and I got another option:

Book1
	L	M	N	O	P	Q	R
1	Team 1	Team 2		Round	Foursome 1	Foursome 2	Foursome 3
2	A	U		1	CDUW	EFXY	ABVZ
3	B	V		2	ACUX	BFVW	DEYZ
4	C	W		3	BCWX	DFVY	AEUZ
5	D	X		4	AEXZ	CFWY	BDUV
6	E	Y		5	CFVX	BEWZ	ADUY
7	F	Z		6	CEUV	BDXY	AFWZ
Sheet3

This one has every player on team 1 playing with every player on team 1 at least once, and every player on team 2 playing with each player on team 2 at least once. But C is never in a foursome with Z, and F is never in a foursome with U. So it just depends on what your priorities are. I may play around a bit more, but I think it's unlikely I can improve on these.

 

[Eric W]

After a few million iterations, I found one where everyone plays with everyone at least once:

Book1
	L	M	N	O	P	Q	R
1	Team 1	Team 2		Round	Foursome 1	Foursome 2	Foursome 3
2	A	U		1	EFXZ	ABUV	CDWY
3	B	V		2	BFVY	DEUZ	ACWX
4	C	W		3	BEWX	CFUY	ADVZ
5	D	X		4	BDYZ	AFUW	CEVX
6	E	Y		5	DEUV	ABXY	CFWZ
7	F	Z		6	BCVW	AEYZ	DFUX
Sheet3

OK, no more tonight.

 

[felixstraube]

Here is my attempt:

You just need 5 rounds for one team to play with each other. But as requested there are 6 round. The Pseudo-random generated sequence can be changed, changing the cell B2 (which can be any number. It is just formated as date).

It is guaranteed that each player is playing every other team member within the first 5 rounds. Last round is random (actually just the first row of a new sequence of 5 rows).

GolfPairing.xlsx
	A	B	C	D	E	F	G
1
2	Rnd Num Gen Seed	17/01/2024		Rounds	Foursome 1	Foursome 2	Foursome 3
3				1	A4 - A5 - B4 - B5	A3 - A6 - B3 - B6	A1 - A2 - B1 - B2
4				2	A4 - A2 - B5 - B6	A1 - A3 - B4 - B2	A5 - A6 - B1 - B3
5				3	A6 - A2 - B6 - B2	A5 - A3 - B5 - B3	A1 - A4 - B1 - B4
6				4	A6 - A4 - B2 - B3	A2 - A3 - B6 - B4	A1 - A5 - B1 - B5
7				5	A3 - A4 - B1 - B6	A1 - A6 - B2 - B5	A2 - A5 - B3 - B4
8				6	A4 - A5 - B3 - B6	A1 - A2 - B1 - B2	A3 - A6 - B4 - B5
Teams + Calculation

Cell Formulas
Range	Formula
E3:G7	E3	=LET( comb, LAMBDA(nt,seed, LET(n,nt, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,n-1,,1),i+2,MOD(i+k*s,n-1)+2), rows,WRAPROWS(TOCOL(res),2), pairs,BYROW(rows,LAMBDA(x,CHOOSECOLS(x,1)&" - "&CHOOSECOLS(x,2))), nPairs,(n-1)*(n/2), randomNumbers,DROP(REDUCE(seed*(2^31-1)/73051,SEQUENCE(nPairs),LAMBDA(x,y,VSTACK(x,MOD(48271*CHOOSEROWS(x,-1),2^31-1)))),1)/(2^31-1), randomOrder,INT(SEQUENCE(nPairs,,0)/(n/2))+1+randomNumbers, orderedPairs,SORTBY(pairs,randomOrder), final,WRAPROWS(orderedPairs,n/2), final) ), n, 12, seed, $B$2, GA, "A"&TEXTSPLIT(TEXTJOIN(" - ", ,comb(n/2, seed)), " - "), GB, "B"&TEXTSPLIT(TEXTJOIN(" - ", ,comb(n/2, seed+15)), " - "), GAc, WRAPROWS(GA, 2), GBc, WRAPROWS(GB, 2), AB, HSTACK(GAc, GBc), res, BYROW(AB, LAMBDA(x, TEXTJOIN(" - ",, x))), WRAPROWS(res, 3) )
E8:G8	E8	=LET( comb, LAMBDA(nt,seed, LET(n,nt, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,n-1,,1),i+2,MOD(i+k*s,n-1)+2), rows,WRAPROWS(TOCOL(res),2), pairs,BYROW(rows,LAMBDA(x,CHOOSECOLS(x,1)&" - "&CHOOSECOLS(x,2))), nPairs,(n-1)*(n/2), randomNumbers,DROP(REDUCE(seed*(2^31-1)/73051,SEQUENCE(nPairs),LAMBDA(x,y,VSTACK(x,MOD(48271*CHOOSEROWS(x,-1),2^31-1)))),1)/(2^31-1), randomOrder,INT(SEQUENCE(nPairs,,0)/(n/2))+1+randomNumbers, orderedPairs,SORTBY(pairs,randomOrder), final,WRAPROWS(orderedPairs,n/2), final) ), n, 12, seed, $B$2+77, GA, "A"&TEXTSPLIT(TEXTJOIN(" - ", ,comb(n/2, seed)), " - "), GB, "B"&TEXTSPLIT(TEXTJOIN(" - ", ,comb(n/2, seed+15)), " - "), GAc, WRAPROWS(GA, 2), GBc, WRAPROWS(GB, 2), AB, HSTACK(GAc, GBc), res, BYROW(AB, LAMBDA(x, TEXTJOIN(" - ",, x))), TAKE(WRAPROWS(res, 3),1) )
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:G8	Expression	=SUM((TEXTSPLIT(E3, " - ")=$K$4)*1)	text	NO
E3:G8	Expression	=SUM((TEXTSPLIT(E3, " - ")=$K$3)*1)	text	NO

Here is the working file to download:

GolfPairing.zip

I added some conditional formatting to highlight the players in cells K3 and K4 for an easy check.