Golf League Scheduling

[cdh121]

Hi everyone:

I have looked through the message boards for solutions to my problem and I don't think I've found anything that quite fits. Here's my challenge...

I run a golf league with 32 teams, so I have 16 tee time slots. Because of time constraints, job responsibilities, etc., my teams fall into 3 general groups:

• Those that can play in any of the tee time slots (1-16)
• Those that can only play in slots 1-8
• Those that can only play in slots 9-16

I am also trying to avoid teams playing each other in back-to-back weeks if possible, but repeats over the course of the season are OK, as are teams not playing each other at all (our season typically runs 16 weeks).

Any help or solutions to this issue are most welcome. If there is an existing solution on these boards that you're aware of, please point me in that direction as well. Thanks in advance.

 

[felixstraube]

Actually I was thinking the other sheets aren't necessary. If you think this work ok I will modify it so everything is on the same sheet and in one formula.

 

[felixstraube]

Here is the unified (and corrected) formula:

File to download: GolfLeagueScheduling3.1.zip

GolfLeagueScheduling3.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Group	Size
2	A	12			Slots	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
3	B	8		Weeks	1	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11
4	C	12			2	A7 - A10	B6 - A8	B8 - A9	B5 - A6	B1 - B3	A11 - A1	B7 - A3	B4 - B2	C5 - A4	C7 - A2	C8 - A5	C10 - C11	C9 - C12	C6 - A12	C1 - C3	C4 - C2
5		32			3	B5 - B3	B1 - B4	A1 - A4	B6 - B2	A9 - A10	B8 - A8	A11 - A7	B7 - A12	C8 - A6	C6 - C2	C9 - A2	C10 - A5	C5 - C3	C11 - C12	C7 - A3	C1 - C4
6					4	A6 - A4	A1 - A5	B7 - B3	B1 - B5	A10 - A11	A7 - A3	B8 - B2	B6 - B4	C11 - A9	C9 - A12	C7 - C3	C1 - C5	C12 - A8	C8 - C2	C6 - C4	C10 - A2
7	Weeks	16			5	B1 - B6	A9 - B2	B7 - B5	A3 - B3	A8 - A4	A7 - A5	A10 - A2	B8 - B4	C9 - C3	C8 - C4	C7 - C5	C11 - A1	C12 - A6	C1 - C6	C10 - C2	A11 - A12
8					6	A1 - B5	A7 - B4	A9 - B3	B1 - B7	A5 - B2	A12 - A2	A8 - A6	B8 - B6	A10 - A4	C9 - C5	C8 - C6	C10 - C4	A11 - A3	C11 - C3	C12 - C2	C1 - C7
9	Seed	16/01/2024			7	A9 - A7	A10 - B5	A6 - B7	B1 - B8	A11 - B6	A5 - B4	A12 - B3	A4 - B2	A2 - C3	C10 - C6	C11 - C5	A3 - A1	C12 - C4	C9 - C7	C1 - C8	A8 - C2
10					8	A1 - B5	A9 - B7	A3 - B2	A4 - B6	A2 - B4	A5 - B3	B1 - A12	A6 - B8	C12 - C6	A10 - C5	C1 - C9	C11 - C7	A8 - C2	C10 - C8	A11 - C3	A7 - C4
11					9	A3 - B6	A6 - B5	B1 - A2	A7 - B7	A11 - B8	B2 - B3	A9 - A12	A8 - B4	A4 - C6	C1 - C10	C2 - C3	A5 - C5	A1 - C4	C11 - C9	A10 - C7	C12 - C8
12					10	A3 - B6	A8 - B8	B1 - A12	A10 - A4	B3 - B4	A7 - B7	A2 - A9	B2 - B5	C1 - C11	C3 - C4	C2 - C5	A5 - C6	A6 - C7	C12 - C10	A1 - C9	A11 - C8
13					11	B3 - B6	A1 - A12	A3 - A10	B4 - B5	B2 - B7	B1 - A2	A11 - A6	A7 - B8	C1 - C12	A5 - C10	C3 - C6	A8 - C8	C2 - C7	A4 - C11	A9 - C9	C4 - C5
14					12	B5 - B6	A1 - A2	B1 - A6	B4 - B7	B3 - B8	A9 - A7	B2 - A8	A4 - A11	C5 - C6	C3 - C8	A3 - C11	C2 - C9	A12 - C10	C1 - A5	C4 - C7	A10 - C12
15					13	B2 - A7	B3 - A10	B1 - A8	B6 - B7	A9 - A6	B5 - B8	A11 - A1	B4 - A3	C3 - C10	C1 - A4	C5 - C8	A2 - C12	A5 - A12	C4 - C9	C2 - C11	C6 - C7
16					14	A1 - A4	B5 - A9	B7 - B8	B2 - A10	B4 - A8	B1 - A11	B6 - A7	B3 - A12	A6 - A2	C2 - A5	C6 - C9	C1 - A3	C3 - C12	C4 - C11	C7 - C8	C5 - C10
17					15	B3 - A6	B6 - A4	B8 - A1	B5 - A5	B4 - A10	B7 - A11	B2 - A7	B1 - A3	C5 - C12	C6 - C11	C8 - C9	C4 - A9	C2 - A12	C3 - A8	C7 - C10	C1 - A2
18					16	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11
Total

Cell Formulas
Range	Formula
F2:U2	F2	=SEQUENCE(,SUM(B2:B4)/2)
E3:E18	E3	=SEQUENCE(B7)
F3:U18	F3	=LET( GAB, LET(n,16, seed, $B$9+100, 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), GAC, LET(n,16, seed, $B$9, 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), GA, LET(n,$B$2, seed, $B$9+50, 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), GABt, BYROW(GAB, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")),TEXTJOIN(" - ",, IF(arr>$B$3, "A1."&TEXT(arr,"00"),"B"&arr))))), GACt, BYROW(GAC, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")), TEXTJOIN(" - ",, IF(arr>$B$4, "A2."&TEXT(arr,"00"),"C"&arr))))), ABC, HSTACK(GABt,GACt), tj, TEXTJOIN(" - ", ,BYROW(ABC, LAMBDA(x, TEXTJOIN(" - ",,x)))), t, TEXTSPLIT(tj, " - "), a, SORT(UNIQUE(FILTER(t, LEFT(t,1)="A"),1), ,,1), s, "A"&SEQUENCE(,COLUMNS(a)), r, IFERROR(XLOOKUP(t, a, s, ,0),t), index, DROP(REDUCE(0, r, LAMBDA(acuArr,newItem, HSTACK(acuArr, TAKE(acuArr,1,-1)+IF(LEFT(newItem,1)="A", 1, 0)))), ,1), v, VALUE(TEXTSPLIT(TEXTJOIN(" - ", ,GA)," - ")), Aarr, HSTACK(v,v), i, IF(LEFT(r,1)="A", "A"&INDEX(Aarr,1, index), r), res, WRAPROWS(BYROW(WRAPROWS(i, 2), LAMBDA(x, TEXTJOIN(" - ",,x))), 16), ress,TAKE(VSTACK(res,res), $B$7), ress )
B5	B5	=SUM(B2:B4)
Dynamic array formulas.

 

[cdh121]

Felix, this is totally awesome and right on the money. A couple of final questions:

1. I assume it's possible to rename my weeks to dates and rename my slots to times if desired...correct?
2. Is it possible to add a sheet to identify my teams by name along with the identifier they get in your schedule sheet?

 

[felixstraube]

Hi, thanks for the feedback.
Yes you can change the week number and the slots number to anything you like.
I added a table with team IDs and team Names and a "schedule" sheet.

File to download: GolfLeagueScheduling3.2.zip

GolfLeagueScheduling3.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Group	Size			Time
2	A	12			08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00		Team ID	Team Name
3	B	8		15/07/2024	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11		A1	Name A1
4	C	12		16/07/2024	A7 - A10	B6 - A8	B8 - A9	B5 - A6	B1 - B3	A11 - A1	B7 - A3	B4 - B2	C5 - A4	C7 - A2	C8 - A5	C10 - C11	C9 - C12	C6 - A12	C1 - C3	C4 - C2		A2	Name A2
5		32		17/07/2024	B5 - B3	B1 - B4	A1 - A4	B6 - B2	A9 - A10	B8 - A8	A11 - A7	B7 - A12	C8 - A6	C6 - C2	C9 - A2	C10 - A5	C5 - C3	C11 - C12	C7 - A3	C1 - C4		A3	Name A3
6				18/07/2024	A6 - A4	A1 - A5	B7 - B3	B1 - B5	A10 - A11	A7 - A3	B8 - B2	B6 - B4	C11 - A9	C9 - A12	C7 - C3	C1 - C5	C12 - A8	C8 - C2	C6 - C4	C10 - A2		A4	Name A4
7	Weeks	16		19/07/2024	B1 - B6	A9 - B2	B7 - B5	A3 - B3	A8 - A4	A7 - A5	A10 - A2	B8 - B4	C9 - C3	C8 - C4	C7 - C5	C11 - A1	C12 - A6	C1 - C6	C10 - C2	A11 - A12		A5	Name A5
8				22/07/2024	A1 - B5	A7 - B4	A9 - B3	B1 - B7	A5 - B2	A12 - A2	A8 - A6	B8 - B6	A10 - A4	C9 - C5	C8 - C6	C10 - C4	A11 - A3	C11 - C3	C12 - C2	C1 - C7		A6	Name A6
9	Seed	16/01/2024		23/07/2024	A9 - A7	A10 - B5	A6 - B7	B1 - B8	A11 - B6	A5 - B4	A12 - B3	A4 - B2	A2 - C3	C10 - C6	C11 - C5	A3 - A1	C12 - C4	C9 - C7	C1 - C8	A8 - C2		A7	Name A7
10				24/07/2024	A1 - B5	A9 - B7	A3 - B2	A4 - B6	A2 - B4	A5 - B3	B1 - A12	A6 - B8	C12 - C6	A10 - C5	C1 - C9	C11 - C7	A8 - C2	C10 - C8	A11 - C3	A7 - C4		A8	Name A8
11				25/07/2024	A3 - B6	A6 - B5	B1 - A2	A7 - B7	A11 - B8	B2 - B3	A9 - A12	A8 - B4	A4 - C6	C1 - C10	C2 - C3	A5 - C5	A1 - C4	C11 - C9	A10 - C7	C12 - C8		A9	Name A9
12				26/07/2024	A3 - B6	A8 - B8	B1 - A12	A10 - A4	B3 - B4	A7 - B7	A2 - A9	B2 - B5	C1 - C11	C3 - C4	C2 - C5	A5 - C6	A6 - C7	C12 - C10	A1 - C9	A11 - C8		A10	Name A10
13				29/07/2024	B3 - B6	A1 - A12	A3 - A10	B4 - B5	B2 - B7	B1 - A2	A11 - A6	A7 - B8	C1 - C12	A5 - C10	C3 - C6	A8 - C8	C2 - C7	A4 - C11	A9 - C9	C4 - C5		A11	Name A11
14				30/07/2024	B5 - B6	A1 - A2	B1 - A6	B4 - B7	B3 - B8	A9 - A7	B2 - A8	A4 - A11	C5 - C6	C3 - C8	A3 - C11	C2 - C9	A12 - C10	C1 - A5	C4 - C7	A10 - C12		A12	Name A12
15				31/07/2024	B2 - A7	B3 - A10	B1 - A8	B6 - B7	A9 - A6	B5 - B8	A11 - A1	B4 - A3	C3 - C10	C1 - A4	C5 - C8	A2 - C12	A5 - A12	C4 - C9	C2 - C11	C6 - C7		A13	Name A13
16				01/08/2024	A1 - A4	B5 - A9	B7 - B8	B2 - A10	B4 - A8	B1 - A11	B6 - A7	B3 - A12	A6 - A2	C2 - A5	C6 - C9	C1 - A3	C3 - C12	C4 - C11	C7 - C8	C5 - C10		A14	Name A14
17				02/08/2024	B3 - A6	B6 - A4	B8 - A1	B5 - A5	B4 - A10	B7 - A11	B2 - A7	B1 - A3	C5 - C12	C6 - C11	C8 - C9	C4 - A9	C2 - A12	C3 - A8	C7 - C10	C1 - A2		B1	Name B1
18				05/08/2024	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11		B2	Name B2
19																						B3	Name B3
20																						B4	Name B4
21																						B5	Name B5
22																						B6	Name B6
23																						B7	Name B7
24																						B8	Name B8
25																						B9	Name B9
26																						B10	Name B10
27																						B11	Name B11
28																						B12	Name B12
29																						B13	Name B13
30																						B14	Name B14
31																						C1	Name C1
32																						C2	Name C2
33																						C3	Name C3
34																						C4	Name C4
35																						C5	Name C5
36																						C6	Name C6
37																						C7	Name C7
38																						C8	Name C8
39																						C9	Name C9
40																						C10	Name C10
41																						C11	Name C11
42																						C12	Name C12
43																						C13	Name C13
44																						C14	Name C14
Teams + Calculation

Cell Formulas
Range	Formula
E3:T18	E3	=LET( tn, $B$5, GAB, LET(n,tn/2, seed, $B$9+100, 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), GAC, LET(n,tn/2, seed, $B$9, 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), GA, LET(n,$B$2, seed, $B$9+50, 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), GABt, BYROW(GAB, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")),TEXTJOIN(" - ",, IF(arr>$B$3, "A1."&TEXT(arr,"00"),"B"&arr))))), GACt, BYROW(GAC, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")), TEXTJOIN(" - ",, IF(arr>$B$4, "A2."&TEXT(arr,"00"),"C"&arr))))), ABC, HSTACK(GABt,GACt), tj, TEXTJOIN(" - ", ,BYROW(ABC, LAMBDA(x, TEXTJOIN(" - ",,x)))), t, TEXTSPLIT(tj, " - "), a, SORT(UNIQUE(FILTER(t, LEFT(t,1)="A"),1), ,,1), s, "A"&SEQUENCE(,COLUMNS(a)), r, IFERROR(XLOOKUP(t, a, s, ,0),t), index, DROP(REDUCE(0, r, LAMBDA(acuArr,newItem, HSTACK(acuArr, TAKE(acuArr,1,-1)+IF(LEFT(newItem,1)="A", 1, 0)))), ,1), v, VALUE(TEXTSPLIT(TEXTJOIN(" - ", ,GA)," - ")), Aarr, HSTACK(v, v, v, v, v, v, v, v), i, IF(LEFT(r,1)="A", "A"&INDEX(Aarr,1, index), r), res, WRAPROWS(BYROW(WRAPROWS(i, 2), LAMBDA(x, TEXTJOIN(" - ",,x))), 16), ress,TAKE(VSTACK(res,res), $B$7), ress )
B4	B4	=32-B2-B3
B5	B5	=SUM(B2:B4)
D4:D18	D4	=WORKDAY(D3,1)
Dynamic array formulas.

Here the schedule ( i didnt include all the rows because of the size)

GolfLeagueScheduling3.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00
2	15/07/2024	Name B7 Name A1	Name B6 Name A2	Name B1 Name B2	Name B3 Name A6	Name A9 Name A7	Name B4 Name A8	Name B5 Name A4	Name B8 Name A11	Name C5 Name A3	Name C1 Name C2	Name C4 Name A12	Name C9 Name C10	Name C3 Name A5	Name C7 Name C12	Name C6 Name A10	Name C8 Name C11
3	16/07/2024	Name A7 Name A10	Name B6 Name A8	Name B8 Name A9	Name B5 Name A6	Name B1 Name B3	Name A11 Name A1	Name B7 Name A3	Name B4 Name B2	Name C5 Name A4	Name C7 Name A2	Name C8 Name A5	Name C10 Name C11	Name C9 Name C12	Name C6 Name A12	Name C1 Name C3	Name C4 Name C2
Schedule

Cell Formulas
Range	Formula
B1:Q1,A2:A3	B1	='Teams + Calculation'!E2
B2:Q3	B2	=TEXTJOIN(CHAR(10), ,XLOOKUP(TEXTSPLIT('Teams + Calculation'!E3, " - "), Teams[[Team ID]:[Team ID]], Teams[[Team Name]:[Team Name]], "not in team list",0))

 

[cdh121]

Felix, this is awesome...and it is the solution I was hoping for. Thank you so much!

 

[felixstraube]

Thanks for the feedback. Happy to help.

Test it and if you find any problem, let me know.

 

[KevCarter]

Thanks for the feedback. Happy to help.

Test it and if you find any problem, let me know.

Felix, thank you for uploading your sheet. Fantastic work, I'm looking forward to learning from it...

 

[felixstraube]

Here is a cleaner version of the formula (i used lambda function instead of repeating 3 times the same code):

GolfLeagueScheduling3.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Group	Size			Time
2	A	12			08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00
3	B	8		15/07/2024	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11
4	C	12		16/07/2024	A7 - A10	B6 - A8	B8 - A9	B5 - A6	B1 - B3	A11 - A1	B7 - A3	B4 - B2	C5 - A4	C7 - A2	C8 - A5	C10 - C11	C9 - C12	C6 - A12	C1 - C3	C4 - C2
5		32		17/07/2024	B5 - B3	B1 - B4	A1 - A4	B6 - B2	A9 - A10	B8 - A8	A11 - A7	B7 - A12	C8 - A6	C6 - C2	C9 - A2	C10 - A5	C5 - C3	C11 - C12	C7 - A3	C1 - C4
6				18/07/2024	A6 - A4	A1 - A5	B7 - B3	B1 - B5	A10 - A11	A7 - A3	B8 - B2	B6 - B4	C11 - A9	C9 - A12	C7 - C3	C1 - C5	C12 - A8	C8 - C2	C6 - C4	C10 - A2
7	Weeks	16		19/07/2024	B1 - B6	A9 - B2	B7 - B5	A3 - B3	A8 - A4	A7 - A5	A10 - A2	B8 - B4	C9 - C3	C8 - C4	C7 - C5	C11 - A1	C12 - A6	C1 - C6	C10 - C2	A11 - A12
8				22/07/2024	A1 - B5	A7 - B4	A9 - B3	B1 - B7	A5 - B2	A12 - A2	A8 - A6	B8 - B6	A10 - A4	C9 - C5	C8 - C6	C10 - C4	A11 - A3	C11 - C3	C12 - C2	C1 - C7
9	Seed	16/01/2024		23/07/2024	A9 - A7	A10 - B5	A6 - B7	B1 - B8	A11 - B6	A5 - B4	A12 - B3	A4 - B2	A2 - C3	C10 - C6	C11 - C5	A3 - A1	C12 - C4	C9 - C7	C1 - C8	A8 - C2
10				24/07/2024	A1 - B5	A9 - B7	A3 - B2	A4 - B6	A2 - B4	A5 - B3	B1 - A12	A6 - B8	C12 - C6	A10 - C5	C1 - C9	C11 - C7	A8 - C2	C10 - C8	A11 - C3	A7 - C4
11				25/07/2024	A3 - B6	A6 - B5	B1 - A2	A7 - B7	A11 - B8	B2 - B3	A9 - A12	A8 - B4	A4 - C6	C1 - C10	C2 - C3	A5 - C5	A1 - C4	C11 - C9	A10 - C7	C12 - C8
12				26/07/2024	A3 - B6	A8 - B8	B1 - A12	A10 - A4	B3 - B4	A7 - B7	A2 - A9	B2 - B5	C1 - C11	C3 - C4	C2 - C5	A5 - C6	A6 - C7	C12 - C10	A1 - C9	A11 - C8
13				29/07/2024	B3 - B6	A1 - A12	A3 - A10	B4 - B5	B2 - B7	B1 - A2	A11 - A6	A7 - B8	C1 - C12	A5 - C10	C3 - C6	A8 - C8	C2 - C7	A4 - C11	A9 - C9	C4 - C5
14				30/07/2024	B5 - B6	A1 - A2	B1 - A6	B4 - B7	B3 - B8	A9 - A7	B2 - A8	A4 - A11	C5 - C6	C3 - C8	A3 - C11	C2 - C9	A12 - C10	C1 - A5	C4 - C7	A10 - C12
15				31/07/2024	B2 - A7	B3 - A10	B1 - A8	B6 - B7	A9 - A6	B5 - B8	A11 - A1	B4 - A3	C3 - C10	C1 - A4	C5 - C8	A2 - C12	A5 - A12	C4 - C9	C2 - C11	C6 - C7
16				01/08/2024	A1 - A4	B5 - A9	B7 - B8	B2 - A10	B4 - A8	B1 - A11	B6 - A7	B3 - A12	A6 - A2	C2 - A5	C6 - C9	C1 - A3	C3 - C12	C4 - C11	C7 - C8	C5 - C10
17				02/08/2024	B3 - A6	B6 - A4	B8 - A1	B5 - A5	B4 - A10	B7 - A11	B2 - A7	B1 - A3	C5 - C12	C6 - C11	C8 - C9	C4 - A9	C2 - A12	C3 - A8	C7 - C10	C1 - A2
18				05/08/2024	B7 - A1	B6 - A2	B1 - B2	B3 - A6	A9 - A7	B4 - A8	B5 - A4	B8 - A11	C5 - A3	C1 - C2	C4 - A12	C9 - C10	C3 - A5	C7 - C12	C6 - A10	C8 - C11
Teams + Calculation

Cell Formulas
Range	Formula
E3:T18	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) ), tn, $B$5, seed, $B$9, GAB, comb(tn/2, seed+100), GAC, comb(tn/2, seed), GA, comb($B$2, seed+50), GABt, BYROW(GAB, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")),TEXTJOIN(" - ",, IF(arr>$B$3, "A1."&TEXT(arr,"00"),"B"&arr))))), GACt, BYROW(GAC, LAMBDA(x, LET(arr, VALUE(TEXTSPLIT(TEXTJOIN(" - ",,x)," - ")), TEXTJOIN(" - ",, IF(arr>$B$4, "A2."&TEXT(arr,"00"),"C"&arr))))), ABC, HSTACK(GABt,GACt), tj, TEXTJOIN(" - ", ,BYROW(ABC, LAMBDA(x, TEXTJOIN(" - ",,x)))), t, TEXTSPLIT(tj, " - "), a, SORT(UNIQUE(FILTER(t, LEFT(t,1)="A"),1), ,,1), s, "A"&SEQUENCE(,COLUMNS(a)), r, IFERROR(XLOOKUP(t, a, s, ,0),t), index, DROP(REDUCE(0, r, LAMBDA(acuArr,newItem, HSTACK(acuArr, TAKE(acuArr,1,-1)+IF(LEFT(newItem,1)="A", 1, 0)))), ,1), v, VALUE(TEXTSPLIT(TEXTJOIN(" - ", ,GA)," - ")), Aarr, HSTACK(v, v, v, v, v, v, v, v), i, IF(LEFT(r,1)="A", "A"&INDEX(Aarr,1, index), r), res, WRAPROWS(BYROW(WRAPROWS(i, 2), LAMBDA(x, TEXTJOIN(" - ",,x))), 16), ress,TAKE(VSTACK(res,res), $B$7), ress )
B4	B4	=32-B2-B3
B5	B5	=SUM(B2:B4)
D4:D18	D4	=WORKDAY(D3,1)
Dynamic array formulas.