Golf League Scheduling

cdh121

New Member
Joined
Jul 21, 2005
Messages
31
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Have you tried using ChatGPT or Google Gemini? Below is a sample solution it provided for me. I'm assuming that you may want to change the Tee Time Slot for Group 'A' teams so you could provide that instruction to the software and have it run the results for you.

1720716741954.png
 
Upvote 0
Maybe you can reuse this, I worked on a while back:


And use 32 as the value n. Or use 16 for the group that can play anytime, and 8 and 8 for the other groups.
How many teams fall into each group? I would need some more details.
 
Upvote 0
Maybe you can reuse this, I worked on a while back:


And use 32 as the value n. Or use 16 for the group that can play anytime, and 8 and 8 for the other groups.
How many teams fall into each group? I would need some more details.
Hi Felix. That looks very good! Would it work with the three groups as described? The size of each group changes a bit from year to year, so if there was a way to enter that group size in, that would be awesome. For the sake of example, let's say each group was roughly equal size...10-12 teams per group, totaling 32 teams. If you need exact numbers, you could use 12 for Group A, 8 for B, 12 for C.
 
Upvote 0
Hi, Maybe this will work?:
File to download: GolfLeagueScheduling.zip

GolfLeagueScheduling.xlsx
ABCDEFGHIJKLMNOPQRSTU
1GroupSizeSlots12345678910111213141516
2A12GroupsBBBBAAAAAACCCCCC
3B8Weeks1B 5 - 6B 1 - 2B 4 - 7B 3 - 8A 5 - 10A 1 - 2A 4 - 11A 3 - 12A 7 - 8A 6 - 9C 5 - 10C 1 - 2C 4 - 11C 3 - 12C 7 - 8C 6 - 9
4C122B 6 - 7B 4 - 2B 1 - 3B 5 - 8A 7 - 10A 8 - 9A 5 - 12A 4 - 2A 6 - 11A 1 - 3C 7 - 10C 8 - 9C 5 - 12C 4 - 2C 6 - 11C 1 - 3
5323B 6 - 2B 7 - 8B 1 - 4B 5 - 3A 1 - 4A 5 - 3A 7 - 12A 6 - 2A 9 - 10A 8 - 11C 1 - 4C 5 - 3C 7 - 12C 6 - 2C 9 - 10C 8 - 11
64B 1 - 5B 6 - 4B 8 - 2B 7 - 3A 7 - 3A 1 - 5A 8 - 2A 9 - 12A 10 - 11A 6 - 4C 7 - 3C 1 - 5C 8 - 2C 9 - 12C 10 - 11C 6 - 4
7Weeks165B 8 - 4B 7 - 5B 2 - 3B 1 - 6A 10 - 2A 8 - 4A 1 - 6A 9 - 3A 7 - 5A 11 - 12C 10 - 2C 8 - 4C 1 - 6C 9 - 3C 7 - 5C 11 - 12
86B 1 - 7B 8 - 6B 2 - 5B 3 - 4A 12 - 2A 11 - 3A 1 - 7A 10 - 4A 9 - 5A 8 - 6C 12 - 2C 11 - 3C 1 - 7C 10 - 4C 9 - 5C 8 - 6
9Seed16/01/20247B 3 - 6B 1 - 8B 4 - 5B 2 - 7A 9 - 7A 10 - 6A 2 - 3A 1 - 8A 11 - 5A 12 - 4C 9 - 7C 10 - 6C 2 - 3C 1 - 8C 11 - 5C 12 - 4
108B 5 - 6B 1 - 2B 4 - 7B 3 - 8A 2 - 5A 1 - 9A 10 - 8A 3 - 4A 11 - 7A 12 - 6C 2 - 5C 1 - 9C 10 - 8C 3 - 4C 11 - 7C 12 - 6
119B 6 - 7B 4 - 2B 1 - 3B 5 - 8A 4 - 5A 12 - 8A 2 - 7A 3 - 6A 11 - 9A 1 - 10C 4 - 5C 12 - 8C 2 - 7C 3 - 6C 11 - 9C 1 - 10
1210B 6 - 2B 7 - 8B 1 - 4B 5 - 3A 5 - 6A 12 - 10A 2 - 9A 4 - 7A 3 - 8A 1 - 11C 5 - 6C 12 - 10C 2 - 9C 4 - 7C 3 - 8C 1 - 11
1311B 1 - 5B 6 - 4B 8 - 2B 7 - 3A 5 - 8A 1 - 12A 6 - 7A 4 - 9A 3 - 10A 2 - 11C 5 - 8C 1 - 12C 6 - 7C 4 - 9C 3 - 10C 2 - 11
1412B 8 - 4B 7 - 5B 2 - 3B 1 - 6A 5 - 10A 1 - 2A 4 - 11A 3 - 12A 7 - 8A 6 - 9C 5 - 10C 1 - 2C 4 - 11C 3 - 12C 7 - 8C 6 - 9
1513B 1 - 7B 8 - 6B 2 - 5B 3 - 4A 7 - 10A 8 - 9A 5 - 12A 4 - 2A 6 - 11A 1 - 3C 7 - 10C 8 - 9C 5 - 12C 4 - 2C 6 - 11C 1 - 3
1614B 3 - 6B 1 - 8B 4 - 5B 2 - 7A 1 - 4A 5 - 3A 7 - 12A 6 - 2A 9 - 10A 8 - 11C 1 - 4C 5 - 3C 7 - 12C 6 - 2C 9 - 10C 8 - 11
1715B 5 - 6B 1 - 2B 4 - 7B 3 - 8A 7 - 3A 1 - 5A 8 - 2A 9 - 12A 10 - 11A 6 - 4C 7 - 3C 1 - 5C 8 - 2C 9 - 12C 10 - 11C 6 - 4
1816B 6 - 7B 4 - 2B 1 - 3B 5 - 8A 10 - 2A 8 - 4A 1 - 6A 9 - 3A 7 - 5A 11 - 12C 10 - 2C 8 - 4C 1 - 6C 9 - 3C 7 - 5C 11 - 12
Total
Cell Formulas
RangeFormula
F1:U1F1=SEQUENCE(,SUM(B2:B4)/2)
F2:U2F2=TEXTSPLIT(REPT(A3&";",B3/2)&";"&REPT(A2&";",B2/2)&";"&REPT(A4&";",B4/2),";",,1)
E3:E18E3=SEQUENCE(B7)
F3:U18F3=LET( GA, "A " &'Group A'!C6#, GB, "B " &'Group B'!C6#, GC, "C " &'Group C'!C6#, weeks, $B$7, weeksSizeGA, TAKE(VSTACK(GA,GA,GA,GA), weeks), weeksSizeGB, TAKE(VSTACK(GB,GB,GB,GB), weeks), weeksSizeGC, TAKE(VSTACK(GC,GC,GC,GC), weeks), HSTACK(weeksSizeGB, weeksSizeGA,weeksSizeGC) )
B5B5=SUM(B2:B4)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:U2Cell Value="C"textNO
F2:U2Cell Value="B"textNO
F2:U2Cell Value="A"textNO


GolfLeagueScheduling.xlsx
ABCDEFGH
1n12(n even)
2Seed16/01/2024
3
4Pairs
5123456
6Weeks15 - 101 - 24 - 113 - 127 - 86 - 9
727 - 108 - 95 - 124 - 26 - 111 - 3
831 - 45 - 37 - 126 - 29 - 108 - 11
947 - 31 - 58 - 29 - 1210 - 116 - 4
10510 - 28 - 41 - 69 - 37 - 511 - 12
11612 - 211 - 31 - 710 - 49 - 58 - 6
1279 - 710 - 62 - 31 - 811 - 512 - 4
1382 - 51 - 910 - 83 - 411 - 712 - 6
1494 - 512 - 82 - 73 - 611 - 91 - 10
15105 - 612 - 102 - 94 - 73 - 81 - 11
16115 - 81 - 126 - 74 - 93 - 102 - 11
Group A
Cell Formulas
RangeFormula
B1B1=Total!B2
B2B2=Total!B9
C5:H5C5=SEQUENCE(,B1/2)
B6:B16B6=SEQUENCE(B1-1)
C6:H16C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.


GolfLeagueScheduling.xlsx
ABCDEF
1n8(n even)
2Seed16/01/2024
3
4Pairs
51234
6Weeks15 - 61 - 24 - 73 - 8
726 - 74 - 21 - 35 - 8
836 - 27 - 81 - 45 - 3
941 - 56 - 48 - 27 - 3
1058 - 47 - 52 - 31 - 6
1161 - 78 - 62 - 53 - 4
1273 - 61 - 84 - 52 - 7
Group B
Cell Formulas
RangeFormula
B1B1=Total!B3
B2B2=Total!B9
C5:F5C5=SEQUENCE(,B1/2)
B6:B12B6=SEQUENCE(B1-1)
C6:F12C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.


GolfLeagueScheduling.xlsx
ABCDEFGH
1n12(n even)
2Seed16/01/2024
3
4Pairs
5123456
6Weeks15 - 101 - 24 - 113 - 127 - 86 - 9
727 - 108 - 95 - 124 - 26 - 111 - 3
831 - 45 - 37 - 126 - 29 - 108 - 11
947 - 31 - 58 - 29 - 1210 - 116 - 4
10510 - 28 - 41 - 69 - 37 - 511 - 12
11612 - 211 - 31 - 710 - 49 - 58 - 6
1279 - 710 - 62 - 31 - 811 - 512 - 4
1382 - 51 - 910 - 83 - 411 - 712 - 6
1494 - 512 - 82 - 73 - 611 - 91 - 10
15105 - 612 - 102 - 94 - 73 - 81 - 11
16115 - 81 - 126 - 74 - 93 - 102 - 11
Group C
Cell Formulas
RangeFormula
B1B1=Total!B4
B2B2=Total!B9
C5:H5C5=SEQUENCE(,B1/2)
B6:B16B6=SEQUENCE(B1-1)
C6:H16C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.



Let me know what you think.
 
Upvote 0
I think that's really close...but teams from the non-time-restricted group A should be able to play teams from B and C. In other words, Group A should be able to play teams from A, B, or C since they can play in any of the time slots; Teams from groups B and C will never play each other because of the restrictions. That allows for more randomness amongst all teams while still keeping restricted teams within their own boundaries. Make sense?
 
Upvote 0
Yes that makes perfect sense. Lets see if I undestood correctly and the new version does what it should.

File to download: GolfLeagueScheduling2.zip

Try it and let me know.

GolfLeagueScheduling2.xlsx
ABCDEFGHIJKLMNOPQRSTU
1GroupSize
2A12Slots12345678910111213141516
3B8Weeks1B7 - A9B6 - A7B1 - B2B3 - A11A1 - A2B4 - A4B5 - A8B8 - A6C5 - A12C1 - C2C4 - A5C9 - C10C3 - A10C7 - C12C6 - A3C8 - C11
4C122A1 - A9B6 - A4B8 - A7B5 - A11B1 - B3A2 - A6B7 - A8B4 - B2C5 - A10C7 - A12C8 - A3C10 - C11C9 - C12C6 - A5C1 - C3C4 - C2
5323B5 - B3B1 - B4A2 - A7B6 - B2A1 - A8B8 - A4A6 - A9B7 - A11C8 - A5C6 - C2C9 - A12C10 - A3C5 - C3C11 - C12C7 - A10C1 - C4
64A2 - A4A1 - A11B7 - B3B1 - B5A6 - A8A9 - A7B8 - B2B6 - B4C11 - A12C9 - A10C7 - C3C1 - C5C12 - A3C8 - C2C6 - C4C10 - A5
7Weeks165B1 - B6A2 - B2B7 - B5A1 - B3A7 - A8A6 - A11A9 - A4B8 - B4C9 - C3C8 - C4C7 - C5C11 - A10C12 - A5C1 - C6C10 - C2A3 - A12
86A1 - B5A2 - B4A6 - B3B1 - B7A9 - B2A7 - A11A8 - A4B8 - B6A3 - A10C9 - C5C8 - C6C10 - C4A12 - A5C11 - C3C12 - C2C1 - C7
9Seed16/01/20247A4 - A11A6 - B5A1 - B7B1 - B8A2 - B6A9 - B4A7 - B3A8 - B2A3 - C3C10 - C6C11 - C5A5 - A10C12 - C4C9 - C7C1 - C8A12 - C2
108A7 - B5A6 - B7A11 - B2A9 - B6A8 - B4A4 - B3B1 - A1A2 - B8C12 - C6A3 - C5C1 - C9C11 - C7A10 - C2C10 - C8A5 - C3A12 - C4
119A8 - B6A4 - B5B1 - A2A7 - B7A9 - B8B2 - B3A6 - A1A11 - B4A12 - C6C1 - C10C2 - C3A5 - C5A10 - C4C11 - C9A3 - C7C12 - C8
1210A11 - B6A8 - B8B1 - A6A9 - A2B3 - B4A4 - B7A7 - A1B2 - B5C1 - C11C3 - C4C2 - C5A10 - C6A5 - C7C12 - C10A3 - C9A12 - C8
1311B3 - B6A8 - A2A7 - A6B4 - B5B2 - B7B1 - A9A4 - A1A11 - B8C1 - C12A12 - C10C3 - C6A10 - C8C2 - C7A3 - C11A5 - C9C4 - C5
1412B5 - B6A8 - A9B1 - A7B4 - B7B3 - B8A11 - A2B2 - A1A4 - A6C5 - C6C3 - C8A5 - C11C2 - C9A10 - C10C1 - A3C4 - C7A12 - C12
1513B2 - A6B3 - A2B1 - A8B6 - B7A4 - A7B5 - B8A11 - A9B4 - A1C3 - C10C1 - A12C5 - C8A10 - C12A5 - A3C4 - C9C2 - C11C6 - C7
1614A11 - A8B5 - A2B7 - B8B2 - A7B4 - A6B1 - A4B6 - A1B3 - A9A10 - A12C2 - A3C6 - C9C1 - A5C3 - C12C4 - C11C7 - C8C5 - C10
1715B3 - A8B6 - A6B8 - A1B5 - A9B4 - A7B7 - A2B2 - A4B1 - A11C5 - C12C6 - C11C8 - C9C4 - A3C2 - A5C3 - A12C7 - C10C1 - A10
1816B7 - A9B6 - A7B1 - B2B3 - A11A1 - A2B4 - A4B5 - A8B8 - A6C5 - A12C1 - C2C4 - A5C9 - C10C3 - A10C7 - C12C6 - A3C8 - C11
Total
Cell Formulas
RangeFormula
F2:U2F2=SEQUENCE(,SUM(B2:B4)/2)
E3:E18E3=SEQUENCE(B7)
F3:U18F3=LET( GAB, 'Group AB'!C6#, GAC, 'Group AC'!C6#, 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), v, VALUE(TEXTSPLIT(TEXTJOIN(" - ", ,'Group A'!C6#)," - ")), Aarr, HSTACK(v,v), i, IF(LEFT(r,1)="A", "A"&INDEX(Aarr,1, TEXTAFTER(r,"A")),r), res, WRAPROWS(BYROW(WRAPROWS(i, 2), LAMBDA(x, TEXTJOIN(" - ",,x))), 16), ress,TAKE(VSTACK(res,res), $B$7), ress )
B5B5=SUM(B2:B4)
Dynamic array formulas.


GolfLeagueScheduling2.xlsx
ABCDEFGHIJ
1n16(n even)
2Seed25/04/2024
3
4Pairs
512345678
6Weeks17 - 126 - 131 - 23 - 169 - 104 - 155 - 148 - 11
729 - 126 - 158 - 135 - 161 - 310 - 117 - 144 - 2
835 - 31 - 410 - 136 - 29 - 148 - 1511 - 127 - 16
9410 - 159 - 167 - 31 - 511 - 1412 - 138 - 26 - 4
1051 - 610 - 27 - 59 - 313 - 1411 - 1612 - 158 - 4
1169 - 510 - 411 - 31 - 712 - 213 - 1614 - 158 - 6
12715 - 1611 - 59 - 71 - 810 - 612 - 413 - 314 - 2
13813 - 511 - 716 - 212 - 614 - 415 - 31 - 910 - 8
14914 - 615 - 51 - 1013 - 712 - 82 - 311 - 916 - 4
151016 - 614 - 81 - 1112 - 103 - 415 - 713 - 92 - 5
16113 - 614 - 1013 - 114 - 52 - 71 - 1215 - 916 - 8
17125 - 614 - 121 - 134 - 73 - 816 - 102 - 915 - 11
18132 - 113 - 101 - 146 - 715 - 135 - 816 - 124 - 9
191416 - 145 - 107 - 82 - 134 - 111 - 156 - 93 - 12
20153 - 146 - 118 - 95 - 124 - 137 - 102 - 151 - 16
Group AB
Cell Formulas
RangeFormula
B2B2=Total!B9+100
C5:J5C5=SEQUENCE(,B1/2)
B6:B20B6=SEQUENCE(B1-1)
C6:J20C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.


GolfLeagueScheduling2.xlsx
ABCDEFGHIJ
1n16(n even)
2Seed16/01/2024
3
4Pairs
512345678
6Weeks15 - 141 - 24 - 159 - 103 - 167 - 126 - 138 - 11
725 - 167 - 148 - 1310 - 119 - 126 - 151 - 34 - 2
838 - 156 - 29 - 1410 - 135 - 311 - 127 - 161 - 4
9411 - 149 - 167 - 31 - 512 - 138 - 26 - 410 - 15
1059 - 38 - 47 - 511 - 1612 - 151 - 610 - 213 - 14
11613 - 169 - 58 - 610 - 414 - 1511 - 312 - 21 - 7
12713 - 310 - 611 - 515 - 1612 - 49 - 71 - 814 - 2
13812 - 613 - 51 - 911 - 716 - 210 - 815 - 314 - 4
14914 - 61 - 102 - 315 - 516 - 411 - 913 - 712 - 8
15101 - 113 - 42 - 516 - 615 - 712 - 1013 - 914 - 8
16111 - 1214 - 103 - 616 - 82 - 713 - 1115 - 94 - 5
17125 - 63 - 815 - 112 - 916 - 101 - 134 - 714 - 12
18133 - 101 - 145 - 816 - 1215 - 134 - 92 - 116 - 7
191416 - 142 - 136 - 91 - 153 - 124 - 117 - 85 - 10
20155 - 126 - 118 - 94 - 132 - 153 - 147 - 101 - 16
Group AC
Cell Formulas
RangeFormula
B2B2=Total!B9
C5:J5C5=SEQUENCE(,B1/2)
B6:B20B6=SEQUENCE(B1-1)
C6:J20C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.


GolfLeagueScheduling2.xlsx
ABCDEFGH
1n12(n even)
2Seed06/03/2024
3
4Pairs
5123456
6Weeks11 - 26 - 97 - 84 - 113 - 125 - 10
727 - 108 - 96 - 111 - 34 - 25 - 12
831 - 49 - 108 - 117 - 126 - 25 - 3
946 - 41 - 510 - 117 - 39 - 128 - 2
1059 - 38 - 47 - 510 - 21 - 611 - 12
1161 - 79 - 512 - 28 - 610 - 411 - 3
1279 - 710 - 611 - 512 - 42 - 31 - 8
1381 - 93 - 42 - 512 - 610 - 811 - 7
1493 - 62 - 711 - 912 - 84 - 51 - 10
15103 - 812 - 104 - 72 - 95 - 61 - 11
16111 - 123 - 102 - 116 - 75 - 84 - 9
Group A
Cell Formulas
RangeFormula
B1B1=Total!B2
B2B2=Total!B9+50
C5:H5C5=SEQUENCE(,B1/2)
B6:B16B6=SEQUENCE(B1-1)
C6:H16C6=LET(n,$B$1, i,SEQUENCE(n-1,,0), k,INT((SEQUENCE(,n-2)+1)/2), s,IF(ISODD(SEQUENCE(,n-2)),1,-1), res,HSTACK(EXPAND(1,$B$1-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(B2*(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)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,127
Members
453,021
Latest member
Justyna P

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