Random Player Generator

[keithrpetty]

I am a member of a golf Society with 20 permanent players. We book 5 time slots x 4 players each week (2 pairs). How do I generate a schedule so that each player plays with a different pairing. There will ultimately be 19 weeks before the schedule reverts back to week 1.

 

[keithrpetty]

I am a member of a golf Society with 20 permanent players. We book 5 time slots x 4 players each week (2 pairs). How do I generate a schedule so that each player plays with a different pairing. There will ultimately be 19 weeks before the schedule reverts back to week 1.

I have an answer, thanks

 

[felixstraube]

Can you share it?

 

[keithrpetty]

I went onto golfsoftware.com and chose twosomes, 20 players and 19 events. It generates various number pairings. I allocated 1 - 20 to each player then did a VLookup.

 

[felixstraube]

Just for fun I researched a little on how to generate the pairs.
Here is what I came up with:
For any even number n you will get your pairs as expected.
The math behind this I got from here: An efficient approach to combinations of pairs in groups without repetitions?

GolfPairs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	n	20
2	(n even)
3			Pairs
4			1	2	3	4	5	6	7	8	9	10
5	Weeks	1	1 - 2	3 - 20	4 - 19	5 - 18	6 - 17	7 - 16	8 - 15	9 - 14	10 - 13	11 - 12
6		2	1 - 3	4 - 2	5 - 20	6 - 19	7 - 18	8 - 17	9 - 16	10 - 15	11 - 14	12 - 13
7		3	1 - 4	5 - 3	6 - 2	7 - 20	8 - 19	9 - 18	10 - 17	11 - 16	12 - 15	13 - 14
8		4	1 - 5	6 - 4	7 - 3	8 - 2	9 - 20	10 - 19	11 - 18	12 - 17	13 - 16	14 - 15
9		5	1 - 6	7 - 5	8 - 4	9 - 3	10 - 2	11 - 20	12 - 19	13 - 18	14 - 17	15 - 16
10		6	1 - 7	8 - 6	9 - 5	10 - 4	11 - 3	12 - 2	13 - 20	14 - 19	15 - 18	16 - 17
11		7	1 - 8	9 - 7	10 - 6	11 - 5	12 - 4	13 - 3	14 - 2	15 - 20	16 - 19	17 - 18
12		8	1 - 9	10 - 8	11 - 7	12 - 6	13 - 5	14 - 4	15 - 3	16 - 2	17 - 20	18 - 19
13		9	1 - 10	11 - 9	12 - 8	13 - 7	14 - 6	15 - 5	16 - 4	17 - 3	18 - 2	19 - 20
14		10	1 - 11	12 - 10	13 - 9	14 - 8	15 - 7	16 - 6	17 - 5	18 - 4	19 - 3	20 - 2
15		11	1 - 12	13 - 11	14 - 10	15 - 9	16 - 8	17 - 7	18 - 6	19 - 5	20 - 4	2 - 3
16		12	1 - 13	14 - 12	15 - 11	16 - 10	17 - 9	18 - 8	19 - 7	20 - 6	2 - 5	3 - 4
17		13	1 - 14	15 - 13	16 - 12	17 - 11	18 - 10	19 - 9	20 - 8	2 - 7	3 - 6	4 - 5
18		14	1 - 15	16 - 14	17 - 13	18 - 12	19 - 11	20 - 10	2 - 9	3 - 8	4 - 7	5 - 6
19		15	1 - 16	17 - 15	18 - 14	19 - 13	20 - 12	2 - 11	3 - 10	4 - 9	5 - 8	6 - 7
20		16	1 - 17	18 - 16	19 - 15	20 - 14	2 - 13	3 - 12	4 - 11	5 - 10	6 - 9	7 - 8
21		17	1 - 18	19 - 17	20 - 16	2 - 15	3 - 14	4 - 13	5 - 12	6 - 11	7 - 10	8 - 9
22		18	1 - 19	20 - 18	2 - 17	3 - 16	4 - 15	5 - 14	6 - 13	7 - 12	8 - 11	9 - 10
23		19	1 - 20	2 - 19	3 - 18	4 - 17	5 - 16	6 - 15	7 - 14	8 - 13	9 - 12	10 - 11
Hoja3 (3)

Cell Formulas
Range	Formula
C4:L4	C4	=SEQUENCE(,B1/2)
B5:B23	B5	=SEQUENCE(B1-1)
C5:L23	C5	=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))), WRAPROWS(pairs,n/2) )
Dynamic array formulas.

 

[felixstraube]

Here is the same thing but the order of players each week is random (so it doesn't always start with player 1):

GolfPairs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	n	20
2	(n even)
3			Pairs
4			1	2	3	4	5	6	7	8	9	10
5	Weeks	1	7 - 16	8 - 15	6 - 17	11 - 12	9 - 14	4 - 19	1 - 2	10 - 13	5 - 18	3 - 20
6		2	4 - 2	7 - 18	10 - 15	1 - 3	9 - 16	5 - 20	8 - 17	6 - 19	12 - 13	11 - 14
7		3	13 - 14	1 - 4	8 - 19	5 - 3	9 - 18	12 - 15	11 - 16	6 - 2	7 - 20	10 - 17
8		4	1 - 5	14 - 15	6 - 4	12 - 17	13 - 16	7 - 3	11 - 18	8 - 2	9 - 20	10 - 19
9		5	8 - 4	11 - 20	12 - 19	10 - 2	9 - 3	14 - 17	13 - 18	15 - 16	1 - 6	7 - 5
10		6	12 - 2	9 - 5	15 - 18	1 - 7	13 - 20	8 - 6	16 - 17	11 - 3	10 - 4	14 - 19
11		7	17 - 18	1 - 8	11 - 5	13 - 3	9 - 7	14 - 2	15 - 20	10 - 6	16 - 19	12 - 4
12		8	14 - 4	10 - 8	12 - 6	15 - 3	13 - 5	18 - 19	11 - 7	1 - 9	17 - 20	16 - 2
13		9	13 - 7	18 - 2	15 - 5	11 - 9	12 - 8	14 - 6	17 - 3	16 - 4	19 - 20	1 - 10
14		10	20 - 2	15 - 7	17 - 5	13 - 9	12 - 10	16 - 6	19 - 3	14 - 8	1 - 11	18 - 4
15		11	15 - 9	1 - 12	20 - 4	13 - 11	19 - 5	16 - 8	14 - 10	2 - 3	18 - 6	17 - 7
16		12	3 - 4	16 - 10	1 - 13	2 - 5	17 - 9	15 - 11	19 - 7	20 - 6	14 - 12	18 - 8
17		13	4 - 5	1 - 14	18 - 10	2 - 7	19 - 9	16 - 12	3 - 6	17 - 11	15 - 13	20 - 8
18		14	5 - 6	17 - 13	3 - 8	16 - 14	19 - 11	20 - 10	1 - 15	4 - 7	2 - 9	18 - 12
19		15	4 - 9	1 - 16	5 - 8	18 - 14	3 - 10	17 - 15	6 - 7	19 - 13	2 - 11	20 - 12
20		16	19 - 15	4 - 11	6 - 9	5 - 10	18 - 16	1 - 17	20 - 14	2 - 13	7 - 8	3 - 12
21		17	7 - 10	1 - 18	8 - 9	4 - 13	3 - 14	5 - 12	19 - 17	6 - 11	20 - 16	2 - 15
22		18	5 - 14	2 - 17	4 - 15	1 - 19	7 - 12	9 - 10	8 - 11	6 - 13	20 - 18	3 - 16
23		19	7 - 14	2 - 19	4 - 17	6 - 15	8 - 13	5 - 16	1 - 20	3 - 18	10 - 11	9 - 12
Random order

Cell Formulas
Range	Formula
C4:L4	C4	=SEQUENCE(,B1/2)
B5:B23	B5	=SEQUENCE(B1-1)
C5:L23	C5	=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), randomOrder,INT(SEQUENCE(nPairs,,0)/(n/2))+1+BYROW(SEQUENCE(nPairs),LAMBDA(x,RAND())), orderedPairs,SORTBY(pairs,randomOrder), final,WRAPROWS(orderedPairs,n/2), final)
Dynamic array formulas.

 

[felixstraube]

One modification more. The random number generation now depends on the seed, so if you need the same distribution twice or just want to remember the last one, put in the same seed as before and you will get the same pairs distribution:

GolfPairs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	n	30	(n even)
2	Seed	16/01/2024
3
4			Pairs
5			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
6	Weeks	1	12 - 21	14 - 19	5 - 28	15 - 18	16 - 17	13 - 20	10 - 23	1 - 2	4 - 29	9 - 24	3 - 30	11 - 22	7 - 26	6 - 27	8 - 25
7		2	8 - 27	1 - 3	16 - 19	14 - 21	6 - 29	12 - 23	9 - 26	10 - 25	5 - 30	11 - 24	15 - 20	7 - 28	4 - 2	13 - 22	17 - 18
8		3	9 - 28	8 - 29	1 - 4	16 - 21	7 - 30	11 - 26	12 - 25	6 - 2	5 - 3	15 - 22	17 - 20	18 - 19	10 - 27	13 - 24	14 - 23
9		4	13 - 26	6 - 4	10 - 29	11 - 28	19 - 20	15 - 24	12 - 27	16 - 23	18 - 21	9 - 30	17 - 22	7 - 3	8 - 2	14 - 25	1 - 5
10		5	14 - 27	10 - 2	1 - 6	18 - 23	17 - 24	15 - 26	16 - 25	11 - 30	19 - 22	9 - 3	13 - 28	8 - 4	7 - 5	20 - 21	12 - 29
11		6	12 - 2	11 - 3	14 - 29	18 - 25	16 - 27	10 - 4	9 - 5	8 - 6	20 - 23	17 - 26	13 - 30	15 - 28	21 - 22	19 - 24	1 - 7
12		7	13 - 3	18 - 27	11 - 5	14 - 2	20 - 25	1 - 8	16 - 29	10 - 6	9 - 7	15 - 30	22 - 23	19 - 26	17 - 28	12 - 4	21 - 24
13		8	1 - 9	20 - 27	21 - 26	23 - 24	10 - 8	19 - 28	17 - 30	14 - 4	18 - 29	11 - 7	12 - 6	15 - 3	22 - 25	13 - 5	16 - 2
14		9	21 - 28	19 - 30	24 - 25	1 - 10	22 - 27	18 - 2	12 - 8	15 - 5	23 - 26	13 - 7	16 - 4	17 - 3	11 - 9	14 - 6	20 - 29
15		10	15 - 7	18 - 4	24 - 27	17 - 5	13 - 9	25 - 26	14 - 8	16 - 6	22 - 29	1 - 11	20 - 2	23 - 28	21 - 30	12 - 10	19 - 3
16		11	20 - 4	14 - 10	21 - 3	16 - 8	22 - 2	13 - 11	26 - 27	23 - 30	18 - 6	1 - 12	15 - 9	17 - 7	25 - 28	24 - 29	19 - 5
17		12	22 - 4	25 - 30	16 - 10	18 - 8	19 - 7	26 - 29	21 - 5	15 - 11	14 - 12	27 - 28	23 - 3	1 - 13	24 - 2	20 - 6	17 - 9
18		13	26 - 2	21 - 7	23 - 5	18 - 10	28 - 29	16 - 12	24 - 4	1 - 14	15 - 13	25 - 3	22 - 6	20 - 8	19 - 9	17 - 11	27 - 30
19		14	17 - 13	18 - 12	27 - 3	20 - 10	23 - 7	29 - 30	19 - 11	24 - 6	28 - 2	1 - 15	26 - 4	25 - 5	16 - 14	21 - 9	22 - 8
20		15	26 - 6	30 - 2	28 - 4	27 - 5	21 - 11	22 - 10	17 - 15	18 - 14	29 - 3	24 - 8	20 - 12	19 - 13	25 - 7	1 - 16	23 - 9
21		16	25 - 9	23 - 11	27 - 7	1 - 17	29 - 5	21 - 13	19 - 15	28 - 6	20 - 14	30 - 4	2 - 3	18 - 16	24 - 10	22 - 12	26 - 8
22		17	30 - 6	21 - 15	27 - 9	2 - 5	20 - 16	19 - 17	28 - 8	26 - 10	1 - 18	29 - 7	3 - 4	23 - 13	25 - 11	22 - 14	24 - 12
23		18	24 - 14	20 - 18	26 - 12	25 - 13	21 - 17	30 - 8	28 - 10	27 - 11	2 - 7	29 - 9	4 - 5	22 - 16	3 - 6	23 - 15	1 - 19
24		19	30 - 10	27 - 13	5 - 6	3 - 8	23 - 17	4 - 7	26 - 14	2 - 9	28 - 12	29 - 11	25 - 15	21 - 19	1 - 20	22 - 18	24 - 16
25		20	28 - 14	30 - 12	26 - 16	27 - 15	25 - 17	24 - 18	6 - 7	3 - 10	29 - 13	4 - 9	23 - 19	22 - 20	2 - 11	1 - 21	5 - 8
26		21	5 - 10	29 - 15	2 - 13	1 - 22	28 - 16	30 - 14	4 - 11	25 - 19	7 - 8	3 - 12	26 - 18	23 - 21	24 - 20	6 - 9	27 - 17
27		22	27 - 19	5 - 12	3 - 14	26 - 20	1 - 23	4 - 13	25 - 21	30 - 16	7 - 10	24 - 22	28 - 18	29 - 17	8 - 9	2 - 15	6 - 11
28		23	4 - 15	8 - 11	25 - 23	27 - 21	7 - 12	5 - 14	1 - 24	9 - 10	28 - 20	3 - 16	26 - 22	30 - 18	29 - 19	6 - 13	2 - 17
29		24	3 - 18	30 - 20	10 - 11	1 - 25	8 - 13	9 - 12	7 - 14	29 - 21	5 - 16	28 - 22	4 - 17	26 - 24	27 - 23	2 - 19	6 - 15
30		25	30 - 22	10 - 13	7 - 16	11 - 12	4 - 19	27 - 25	6 - 17	9 - 14	5 - 18	8 - 15	28 - 24	29 - 23	3 - 20	1 - 26	2 - 21
31		26	6 - 19	3 - 22	7 - 18	12 - 13	5 - 20	1 - 27	30 - 24	4 - 21	28 - 26	2 - 23	29 - 25	11 - 14	10 - 15	9 - 16	8 - 17
32		27	10 - 17	29 - 27	13 - 14	8 - 19	5 - 22	3 - 24	11 - 16	4 - 23	9 - 18	7 - 20	2 - 25	12 - 15	30 - 26	1 - 28	6 - 21
33		28	7 - 22	30 - 28	9 - 20	10 - 19	5 - 24	13 - 16	8 - 21	1 - 29	12 - 17	2 - 27	11 - 18	3 - 26	6 - 23	4 - 25	14 - 15
34		29	15 - 16	3 - 28	1 - 30	6 - 25	7 - 24	4 - 27	14 - 17	8 - 23	9 - 22	10 - 21	5 - 26	13 - 18	2 - 29	11 - 20	12 - 19
Random order With Seed

Cell Formulas
Range	Formula
C5:Q5	C5	=SEQUENCE(,B1/2)
B6:B34	B6	=SEQUENCE(B1-1)
C6:Q34	C6	=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.

 

[keithrpetty]

WOW, very intersting. Thanks for your help.

 

[felixstraube]

You are welcome. This was a fun one to solve. Thanks for the feedback!