Automated rotating partners

[upwordz]

I have a class of 18 students who will meet 30 times this semester. I want to assign students into new groups of 2 for each class. I want to ensure everyone has a chance to work together. No idea where to begin with a formula. Hoping to get some help.

 

[lrobbo314]

Something like this?

EXCEL
	H	I	J	K	L	M	N	O	P	Q
1	Group 1	Group 2		Week	Student 1	Student 2		Week	Student 1	Student 2
2	CLEMENTINE	HUGHIE		Week 1	CLEMENTINE	HUGHIE		Week 1	CLEMENTINE	HUGHIE
3	SAVANNA	CONSTANCE		Week 2	CLEMENTINE	CONSTANCE		Week 1	SAVANNA	HUGHIE
4	LACEY-MAE	AKRAM		Week 3	CLEMENTINE	AKRAM		Week 1	LACEY-MAE	HUGHIE
5	TIFFANY	CARLOS		Week 4	CLEMENTINE	CARLOS		Week 1	TIFFANY	HUGHIE
6	QUINN	PETRA		Week 5	CLEMENTINE	PETRA		Week 1	QUINN	HUGHIE
7	JAKE	CHAYA		Week 6	CLEMENTINE	CHAYA		Week 1	JAKE	HUGHIE
8	IVIE	CASSIE		Week 7	CLEMENTINE	CASSIE		Week 1	IVIE	HUGHIE
9	ELIO	OLIVIA-GRACE		Week 8	CLEMENTINE	OLIVIA-GRACE		Week 1	ELIO	HUGHIE
10	MIRIAM	LORCAN		Week 9	CLEMENTINE	LORCAN		Week 1	MIRIAM	HUGHIE
11				Week 1	SAVANNA	HUGHIE		Week 2	CLEMENTINE	CONSTANCE
12				Week 2	SAVANNA	CONSTANCE		Week 2	SAVANNA	CONSTANCE
13				Week 3	SAVANNA	AKRAM		Week 2	LACEY-MAE	CONSTANCE
14				Week 4	SAVANNA	CARLOS		Week 2	TIFFANY	CONSTANCE
15				Week 5	SAVANNA	PETRA		Week 2	QUINN	CONSTANCE
16				Week 6	SAVANNA	CHAYA		Week 2	JAKE	CONSTANCE
17				Week 7	SAVANNA	CASSIE		Week 2	IVIE	CONSTANCE
18				Week 8	SAVANNA	OLIVIA-GRACE		Week 2	ELIO	CONSTANCE
19				Week 9	SAVANNA	LORCAN		Week 2	MIRIAM	CONSTANCE
20				Week 1	LACEY-MAE	HUGHIE		Week 3	CLEMENTINE	AKRAM
21				Week 2	LACEY-MAE	CONSTANCE		Week 3	SAVANNA	AKRAM
22				Week 3	LACEY-MAE	AKRAM		Week 3	LACEY-MAE	AKRAM
23				Week 4	LACEY-MAE	CARLOS		Week 3	TIFFANY	AKRAM
24				Week 5	LACEY-MAE	PETRA		Week 3	QUINN	AKRAM
25				Week 6	LACEY-MAE	CHAYA		Week 3	JAKE	AKRAM
26				Week 7	LACEY-MAE	CASSIE		Week 3	IVIE	AKRAM
27				Week 8	LACEY-MAE	OLIVIA-GRACE		Week 3	ELIO	AKRAM
28				Week 9	LACEY-MAE	LORCAN		Week 3	MIRIAM	AKRAM
29				Week 1	TIFFANY	HUGHIE		Week 4	CLEMENTINE	CARLOS
30				Week 2	TIFFANY	CONSTANCE		Week 4	SAVANNA	CARLOS
31				Week 3	TIFFANY	AKRAM		Week 4	LACEY-MAE	CARLOS
32				Week 4	TIFFANY	CARLOS		Week 4	TIFFANY	CARLOS
33				Week 5	TIFFANY	PETRA		Week 4	QUINN	CARLOS
34				Week 6	TIFFANY	CHAYA		Week 4	JAKE	CARLOS
35				Week 7	TIFFANY	CASSIE		Week 4	IVIE	CARLOS
36				Week 8	TIFFANY	OLIVIA-GRACE		Week 4	ELIO	CARLOS
37				Week 9	TIFFANY	LORCAN		Week 4	MIRIAM	CARLOS
38				Week 1	QUINN	HUGHIE		Week 5	CLEMENTINE	PETRA
39				Week 2	QUINN	CONSTANCE		Week 5	SAVANNA	PETRA
40				Week 3	QUINN	AKRAM		Week 5	LACEY-MAE	PETRA
41				Week 4	QUINN	CARLOS		Week 5	TIFFANY	PETRA
42				Week 5	QUINN	PETRA		Week 5	QUINN	PETRA
43				Week 6	QUINN	CHAYA		Week 5	JAKE	PETRA
44				Week 7	QUINN	CASSIE		Week 5	IVIE	PETRA
45				Week 8	QUINN	OLIVIA-GRACE		Week 5	ELIO	PETRA
46				Week 9	QUINN	LORCAN		Week 5	MIRIAM	PETRA
47				Week 1	JAKE	HUGHIE		Week 6	CLEMENTINE	CHAYA
48				Week 2	JAKE	CONSTANCE		Week 6	SAVANNA	CHAYA
49				Week 3	JAKE	AKRAM		Week 6	LACEY-MAE	CHAYA
50				Week 4	JAKE	CARLOS		Week 6	TIFFANY	CHAYA
51				Week 5	JAKE	PETRA		Week 6	QUINN	CHAYA
52				Week 6	JAKE	CHAYA		Week 6	JAKE	CHAYA
53				Week 7	JAKE	CASSIE		Week 6	IVIE	CHAYA
54				Week 8	JAKE	OLIVIA-GRACE		Week 6	ELIO	CHAYA
55				Week 9	JAKE	LORCAN		Week 6	MIRIAM	CHAYA
56				Week 1	IVIE	HUGHIE		Week 7	CLEMENTINE	CASSIE
57				Week 2	IVIE	CONSTANCE		Week 7	SAVANNA	CASSIE
58				Week 3	IVIE	AKRAM		Week 7	LACEY-MAE	CASSIE
59				Week 4	IVIE	CARLOS		Week 7	TIFFANY	CASSIE
60				Week 5	IVIE	PETRA		Week 7	QUINN	CASSIE
61				Week 6	IVIE	CHAYA		Week 7	JAKE	CASSIE
62				Week 7	IVIE	CASSIE		Week 7	IVIE	CASSIE
63				Week 8	IVIE	OLIVIA-GRACE		Week 7	ELIO	CASSIE
64				Week 9	IVIE	LORCAN		Week 7	MIRIAM	CASSIE
65				Week 1	ELIO	HUGHIE		Week 8	CLEMENTINE	OLIVIA-GRACE
66				Week 2	ELIO	CONSTANCE		Week 8	SAVANNA	OLIVIA-GRACE
67				Week 3	ELIO	AKRAM		Week 8	LACEY-MAE	OLIVIA-GRACE
68				Week 4	ELIO	CARLOS		Week 8	TIFFANY	OLIVIA-GRACE
69				Week 5	ELIO	PETRA		Week 8	QUINN	OLIVIA-GRACE
70				Week 6	ELIO	CHAYA		Week 8	JAKE	OLIVIA-GRACE
71				Week 7	ELIO	CASSIE		Week 8	IVIE	OLIVIA-GRACE
72				Week 8	ELIO	OLIVIA-GRACE		Week 8	ELIO	OLIVIA-GRACE
73				Week 9	ELIO	LORCAN		Week 8	MIRIAM	OLIVIA-GRACE
74				Week 1	MIRIAM	HUGHIE		Week 9	CLEMENTINE	LORCAN
75				Week 2	MIRIAM	CONSTANCE		Week 9	SAVANNA	LORCAN
76				Week 3	MIRIAM	AKRAM		Week 9	LACEY-MAE	LORCAN
77				Week 4	MIRIAM	CARLOS		Week 9	TIFFANY	LORCAN
78				Week 5	MIRIAM	PETRA		Week 9	QUINN	LORCAN
79				Week 6	MIRIAM	CHAYA		Week 9	JAKE	LORCAN
80				Week 7	MIRIAM	CASSIE		Week 9	IVIE	LORCAN
81				Week 8	MIRIAM	OLIVIA-GRACE		Week 9	ELIO	LORCAN
82				Week 9	MIRIAM	LORCAN		Week 9	MIRIAM	LORCAN
Sheet1

Cell Formulas
Range	Formula
K2:K82	K2	="Week " &MOD(SEQUENCE(81,,0),9)+1
L2:M82	L2	=DROP(REDUCE("",TOCOL(H2:H10&"|"&TOROW(I2:I10)),LAMBDA(s,c,VSTACK(s,TEXTSPLIT(c,"|")))),1)
O2:Q82	O2	=SORT(K2:M82,1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:I10	Expression	=MOD(ROW(),2)=0	text	YES
K2:M82	Expression	=MOD(ROW(),2)=0	text	YES
O2:Q82	Expression	=MOD(ROW(),2)=0	text	YES

 

[lrobbo314]

Realized that last post only had 1/2 of the total combinations. This one gets them all.

EXCEL
	G	H	I	J	K
1	Students		Week	Student 1	Student 2
2	SIYANA		Week 1	SIYANA	LEXI-MAE
3	LEXI-MAE		Week 2	SIYANA	KENDRICK
4	KENDRICK		Week 3	SIYANA	STEPHANIE
5	STEPHANIE		Week 4	SIYANA	WARREN
6	WARREN		Week 5	SIYANA	RUQAYYAH
7	RUQAYYAH		Week 6	SIYANA	VERA
8	VERA		Week 7	SIYANA	HUW
9	HUW		Week 8	SIYANA	ORLAGH
10	ORLAGH		Week 9	SIYANA	SAFA
11	SAFA		Week 10	SIYANA	DILLON
12	DILLON		Week 11	SIYANA	NICOLE
13	NICOLE		Week 12	SIYANA	AMYRA
14	AMYRA		Week 13	SIYANA	SKY
15	SKY		Week 14	SIYANA	LUAN
16	LUAN		Week 15	SIYANA	NAIRA
17	NAIRA		Week 16	SIYANA	ALESSANDRO
18	ALESSANDRO		Week 17	SIYANA	FERNE
19	FERNE		Week 18	LEXI-MAE	SIYANA
20			Week 1	LEXI-MAE	KENDRICK
21			Week 2	LEXI-MAE	STEPHANIE
22			Week 3	LEXI-MAE	WARREN
23			Week 4	LEXI-MAE	RUQAYYAH
24			Week 5	LEXI-MAE	VERA
25			Week 6	LEXI-MAE	HUW
26			Week 7	LEXI-MAE	ORLAGH
27			Week 8	LEXI-MAE	SAFA
28			Week 9	LEXI-MAE	DILLON
29			Week 10	LEXI-MAE	NICOLE
30			Week 11	LEXI-MAE	AMYRA
31			Week 12	LEXI-MAE	SKY
32			Week 13	LEXI-MAE	LUAN
33			Week 14	LEXI-MAE	NAIRA
34			Week 15	LEXI-MAE	ALESSANDRO
35			Week 16	LEXI-MAE	FERNE
36			Week 17	KENDRICK	SIYANA
37			Week 18	KENDRICK	LEXI-MAE
38			Week 1	KENDRICK	STEPHANIE
39			Week 2	KENDRICK	WARREN
40			Week 3	KENDRICK	RUQAYYAH
41			Week 4	KENDRICK	VERA
42			Week 5	KENDRICK	HUW
43			Week 6	KENDRICK	ORLAGH
44			Week 7	KENDRICK	SAFA
45			Week 8	KENDRICK	DILLON
46			Week 9	KENDRICK	NICOLE
47			Week 10	KENDRICK	AMYRA
48			Week 11	KENDRICK	SKY
49			Week 12	KENDRICK	LUAN
50			Week 13	KENDRICK	NAIRA
51			Week 14	KENDRICK	ALESSANDRO
52			Week 15	KENDRICK	FERNE
53			Week 16	STEPHANIE	SIYANA
54			Week 17	STEPHANIE	LEXI-MAE
55			Week 18	STEPHANIE	KENDRICK
56			Week 1	STEPHANIE	WARREN
57			Week 2	STEPHANIE	RUQAYYAH
58			Week 3	STEPHANIE	VERA
59			Week 4	STEPHANIE	HUW
60			Week 5	STEPHANIE	ORLAGH
61			Week 6	STEPHANIE	SAFA
62			Week 7	STEPHANIE	DILLON
63			Week 8	STEPHANIE	NICOLE
64			Week 9	STEPHANIE	AMYRA
65			Week 10	STEPHANIE	SKY
66			Week 11	STEPHANIE	LUAN
67			Week 12	STEPHANIE	NAIRA
68			Week 13	STEPHANIE	ALESSANDRO
69			Week 14	STEPHANIE	FERNE
70			Week 15	WARREN	SIYANA
71			Week 16	WARREN	LEXI-MAE
72			Week 17	WARREN	KENDRICK
73			Week 18	WARREN	STEPHANIE
74			Week 1	WARREN	RUQAYYAH
75			Week 2	WARREN	VERA
76			Week 3	WARREN	HUW
77			Week 4	WARREN	ORLAGH
78			Week 5	WARREN	SAFA
79			Week 6	WARREN	DILLON
80			Week 7	WARREN	NICOLE
81			Week 8	WARREN	AMYRA
82			Week 9	WARREN	SKY
83			Week 10	WARREN	LUAN
84			Week 11	WARREN	NAIRA
85			Week 12	WARREN	ALESSANDRO
86			Week 13	WARREN	FERNE
87			Week 14	RUQAYYAH	SIYANA
88			Week 15	RUQAYYAH	LEXI-MAE
89			Week 16	RUQAYYAH	KENDRICK
90			Week 17	RUQAYYAH	STEPHANIE
91			Week 18	RUQAYYAH	WARREN
92			Week 1	RUQAYYAH	VERA
93			Week 2	RUQAYYAH	HUW
94			Week 3	RUQAYYAH	ORLAGH
95			Week 4	RUQAYYAH	SAFA
96			Week 5	RUQAYYAH	DILLON
97			Week 6	RUQAYYAH	NICOLE
98			Week 7	RUQAYYAH	AMYRA
99			Week 8	RUQAYYAH	SKY
100			Week 9	RUQAYYAH	LUAN
101			Week 10	RUQAYYAH	NAIRA
102			Week 11	RUQAYYAH	ALESSANDRO
103			Week 12	RUQAYYAH	FERNE
104			Week 13	VERA	SIYANA
105			Week 14	VERA	LEXI-MAE
106			Week 15	VERA	KENDRICK
107			Week 16	VERA	STEPHANIE
108			Week 17	VERA	WARREN
109			Week 18	VERA	RUQAYYAH
110			Week 1	VERA	HUW
111			Week 2	VERA	ORLAGH
112			Week 3	VERA	SAFA
113			Week 4	VERA	DILLON
114			Week 5	VERA	NICOLE
115			Week 6	VERA	AMYRA
116			Week 7	VERA	SKY
117			Week 8	VERA	LUAN
118			Week 9	VERA	NAIRA
119			Week 10	VERA	ALESSANDRO
120			Week 11	VERA	FERNE
121			Week 12	HUW	SIYANA
122			Week 13	HUW	LEXI-MAE
123			Week 14	HUW	KENDRICK
124			Week 15	HUW	STEPHANIE
125			Week 16	HUW	WARREN
126			Week 17	HUW	RUQAYYAH
127			Week 18	HUW	VERA
128			Week 1	HUW	ORLAGH
129			Week 2	HUW	SAFA
130			Week 3	HUW	DILLON
131			Week 4	HUW	NICOLE
132			Week 5	HUW	AMYRA
133			Week 6	HUW	SKY
134			Week 7	HUW	LUAN
135			Week 8	HUW	NAIRA
136			Week 9	HUW	ALESSANDRO
137			Week 10	HUW	FERNE
138			Week 11	ORLAGH	SIYANA
139			Week 12	ORLAGH	LEXI-MAE
140			Week 13	ORLAGH	KENDRICK
141			Week 14	ORLAGH	STEPHANIE
142			Week 15	ORLAGH	WARREN
143			Week 16	ORLAGH	RUQAYYAH
144			Week 17	ORLAGH	VERA
145			Week 18	ORLAGH	HUW
146			Week 1	ORLAGH	SAFA
147			Week 2	ORLAGH	DILLON
148			Week 3	ORLAGH	NICOLE
149			Week 4	ORLAGH	AMYRA
150			Week 5	ORLAGH	SKY
151			Week 6	ORLAGH	LUAN
152			Week 7	ORLAGH	NAIRA
153			Week 8	ORLAGH	ALESSANDRO
154			Week 9	ORLAGH	FERNE
155			Week 10	SAFA	SIYANA
156			Week 11	SAFA	LEXI-MAE
157			Week 12	SAFA	KENDRICK
158			Week 13	SAFA	STEPHANIE
159			Week 14	SAFA	WARREN
160			Week 15	SAFA	RUQAYYAH
161			Week 16	SAFA	VERA
162			Week 17	SAFA	HUW
163			Week 18	SAFA	ORLAGH
164			Week 1	SAFA	DILLON
165			Week 2	SAFA	NICOLE
166			Week 3	SAFA	AMYRA
167			Week 4	SAFA	SKY
168			Week 5	SAFA	LUAN
169			Week 6	SAFA	NAIRA
170			Week 7	SAFA	ALESSANDRO
171			Week 8	SAFA	FERNE
172			Week 9	DILLON	SIYANA
173			Week 10	DILLON	LEXI-MAE
174			Week 11	DILLON	KENDRICK
175			Week 12	DILLON	STEPHANIE
176			Week 13	DILLON	WARREN
177			Week 14	DILLON	RUQAYYAH
178			Week 15	DILLON	VERA
179			Week 16	DILLON	HUW
180			Week 17	DILLON	ORLAGH
181			Week 18	DILLON	SAFA
182			Week 1	DILLON	NICOLE
183			Week 2	DILLON	AMYRA
184			Week 3	DILLON	SKY
185			Week 4	DILLON	LUAN
186			Week 5	DILLON	NAIRA
187			Week 6	DILLON	ALESSANDRO
188			Week 7	DILLON	FERNE
189			Week 8	NICOLE	SIYANA
190			Week 9	NICOLE	LEXI-MAE
191			Week 10	NICOLE	KENDRICK
192			Week 11	NICOLE	STEPHANIE
193			Week 12	NICOLE	WARREN
194			Week 13	NICOLE	RUQAYYAH
195			Week 14	NICOLE	VERA
196			Week 15	NICOLE	HUW
197			Week 16	NICOLE	ORLAGH
198			Week 17	NICOLE	SAFA
199			Week 18	NICOLE	DILLON
200			Week 1	NICOLE	AMYRA
201			Week 2	NICOLE	SKY
202			Week 3	NICOLE	LUAN
203			Week 4	NICOLE	NAIRA
204			Week 5	NICOLE	ALESSANDRO
205			Week 6	NICOLE	FERNE
206			Week 7	AMYRA	SIYANA
207			Week 8	AMYRA	LEXI-MAE
208			Week 9	AMYRA	KENDRICK
209			Week 10	AMYRA	STEPHANIE
210			Week 11	AMYRA	WARREN
211			Week 12	AMYRA	RUQAYYAH
212			Week 13	AMYRA	VERA
213			Week 14	AMYRA	HUW
214			Week 15	AMYRA	ORLAGH
215			Week 16	AMYRA	SAFA
216			Week 17	AMYRA	DILLON
217			Week 18	AMYRA	NICOLE
218			Week 1	AMYRA	SKY
219			Week 2	AMYRA	LUAN
220			Week 3	AMYRA	NAIRA
221			Week 4	AMYRA	ALESSANDRO
222			Week 5	AMYRA	FERNE
223			Week 6	SKY	SIYANA
224			Week 7	SKY	LEXI-MAE
225			Week 8	SKY	KENDRICK
226			Week 9	SKY	STEPHANIE
227			Week 10	SKY	WARREN
228			Week 11	SKY	RUQAYYAH
229			Week 12	SKY	VERA
230			Week 13	SKY	HUW
231			Week 14	SKY	ORLAGH
232			Week 15	SKY	SAFA
233			Week 16	SKY	DILLON
234			Week 17	SKY	NICOLE
235			Week 18	SKY	AMYRA
236			Week 1	SKY	LUAN
237			Week 2	SKY	NAIRA
238			Week 3	SKY	ALESSANDRO
239			Week 4	SKY	FERNE
240			Week 5	LUAN	SIYANA
241			Week 6	LUAN	LEXI-MAE
242			Week 7	LUAN	KENDRICK
243			Week 8	LUAN	STEPHANIE
244			Week 9	LUAN	WARREN
245			Week 10	LUAN	RUQAYYAH
246			Week 11	LUAN	VERA
247			Week 12	LUAN	HUW
248			Week 13	LUAN	ORLAGH
249			Week 14	LUAN	SAFA
250			Week 15	LUAN	DILLON
251			Week 16	LUAN	NICOLE
252			Week 17	LUAN	AMYRA
253			Week 18	LUAN	SKY
254			Week 1	LUAN	NAIRA
255			Week 2	LUAN	ALESSANDRO
256			Week 3	LUAN	FERNE
257			Week 4	NAIRA	SIYANA
258			Week 5	NAIRA	LEXI-MAE
259			Week 6	NAIRA	KENDRICK
260			Week 7	NAIRA	STEPHANIE
261			Week 8	NAIRA	WARREN
262			Week 9	NAIRA	RUQAYYAH
263			Week 10	NAIRA	VERA
264			Week 11	NAIRA	HUW
265			Week 12	NAIRA	ORLAGH
266			Week 13	NAIRA	SAFA
267			Week 14	NAIRA	DILLON
268			Week 15	NAIRA	NICOLE
269			Week 16	NAIRA	AMYRA
270			Week 17	NAIRA	SKY
271			Week 18	NAIRA	LUAN
272			Week 1	NAIRA	ALESSANDRO
273			Week 2	NAIRA	FERNE
274			Week 3	ALESSANDRO	SIYANA
275			Week 4	ALESSANDRO	LEXI-MAE
276			Week 5	ALESSANDRO	KENDRICK
277			Week 6	ALESSANDRO	STEPHANIE
278			Week 7	ALESSANDRO	WARREN
279			Week 8	ALESSANDRO	RUQAYYAH
280			Week 9	ALESSANDRO	VERA
281			Week 10	ALESSANDRO	HUW
282			Week 11	ALESSANDRO	ORLAGH
283			Week 12	ALESSANDRO	SAFA
284			Week 13	ALESSANDRO	DILLON
285			Week 14	ALESSANDRO	NICOLE
286			Week 15	ALESSANDRO	AMYRA
287			Week 16	ALESSANDRO	SKY
288			Week 17	ALESSANDRO	LUAN
289			Week 18	ALESSANDRO	NAIRA
290			Week 1	ALESSANDRO	FERNE
291			Week 2	FERNE	SIYANA
292			Week 3	FERNE	LEXI-MAE
293			Week 4	FERNE	KENDRICK
294			Week 5	FERNE	STEPHANIE
295			Week 6	FERNE	WARREN
296			Week 7	FERNE	RUQAYYAH
297			Week 8	FERNE	VERA
298			Week 9	FERNE	HUW
299			Week 10	FERNE	ORLAGH
300			Week 11	FERNE	SAFA
301			Week 12	FERNE	DILLON
302			Week 13	FERNE	NICOLE
303			Week 14	FERNE	AMYRA
304			Week 15	FERNE	SKY
305			Week 16	FERNE	LUAN
306			Week 17	FERNE	NAIRA
307			Week 18	FERNE	ALESSANDRO
Sheet1

Cell Formulas
Range	Formula
I2:I307	I2	="Week "&MOD(SEQUENCE(ROWS(J2#),,0),ROWS(G2:G19))+1
J2:K307	J2	=LET(s,G2:G19,r,TOROW(s),p,IF(s=r,"",s&"|"&r),tc,TOCOL(p),f,FILTER(tc,tc<>""),DROP(REDUCE("",f,LAMBDA(s,c,VSTACK(s,TEXTSPLIT(c,"|")))),1))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:K307	Expression	=MOD(ROW(),2)=0	text	YES

 

[lrobbo314]

Noticed an issue with the last one as well.

This should be the correct version.

The combinations only allow for 8 full weeks of combinations. There is a half week at the bottom of the list.

After 8 weeks you could just start from the top again.

EXCEL
	G	H	I	J	K	L	M	N
1	Students		Student 1	Student 2		Week	Student 1	Student 2
2	ALESSANDRO		ALESSANDRO	AMYRA		Week 1	ALESSANDRO	AMYRA
3	AMYRA		ALESSANDRO	DILLON		Week 1	AMYRA	FERNE
4	DILLON		ALESSANDRO	FERNE		Week 1	DILLON	LEXI-MAE
5	FERNE		ALESSANDRO	HUW		Week 1	FERNE	ORLAGH
6	HUW		ALESSANDRO	KENDRICK		Week 1	HUW	STEPHANIE
7	KENDRICK		ALESSANDRO	LEXI-MAE		Week 1	LEXI-MAE	ORLAGH
8	LEXI-MAE		ALESSANDRO	LUAN		Week 1	NAIRA	NICOLE
9	LUAN		ALESSANDRO	NAIRA		Week 1	ORLAGH	SAFA
10	NAIRA		ALESSANDRO	NICOLE		Week 1	SIYANA	STEPHANIE
11	NICOLE		ALESSANDRO	ORLAGH		Week 1	ALESSANDRO	DILLON
12	ORLAGH		ALESSANDRO	RUQAYYAH		Week 1	AMYRA	HUW
13	RUQAYYAH		ALESSANDRO	SAFA		Week 1	DILLON	LUAN
14	SAFA		ALESSANDRO	SIYANA		Week 1	FERNE	RUQAYYAH
15	SIYANA		ALESSANDRO	SKY		Week 1	HUW	VERA
16	SKY		ALESSANDRO	STEPHANIE		Week 1	LEXI-MAE	RUQAYYAH
17	STEPHANIE		ALESSANDRO	VERA		Week 1	NAIRA	ORLAGH
18	VERA		ALESSANDRO	WARREN		Week 1	ORLAGH	SIYANA
19	WARREN		AMYRA	DILLON		Week 1	SIYANA	VERA
20			AMYRA	FERNE		Week 2	ALESSANDRO	FERNE
21			AMYRA	HUW		Week 2	AMYRA	KENDRICK
22			AMYRA	KENDRICK		Week 2	DILLON	NAIRA
23			AMYRA	LEXI-MAE		Week 2	FERNE	SAFA
24			AMYRA	LUAN		Week 2	HUW	WARREN
25			AMYRA	NAIRA		Week 2	LEXI-MAE	SAFA
26			AMYRA	NICOLE		Week 2	NAIRA	RUQAYYAH
27			AMYRA	ORLAGH		Week 2	ORLAGH	SKY
28			AMYRA	RUQAYYAH		Week 2	SIYANA	WARREN
29			AMYRA	SAFA		Week 2	ALESSANDRO	HUW
30			AMYRA	SIYANA		Week 2	AMYRA	LEXI-MAE
31			AMYRA	SKY		Week 2	DILLON	NICOLE
32			AMYRA	STEPHANIE		Week 2	FERNE	SIYANA
33			AMYRA	VERA		Week 2	KENDRICK	LEXI-MAE
34			AMYRA	WARREN		Week 2	LEXI-MAE	SIYANA
35			DILLON	FERNE		Week 2	NAIRA	SAFA
36			DILLON	HUW		Week 2	ORLAGH	STEPHANIE
37			DILLON	KENDRICK		Week 2	SKY	STEPHANIE
38			DILLON	LEXI-MAE		Week 3	ALESSANDRO	KENDRICK
39			DILLON	LUAN		Week 3	AMYRA	LUAN
40			DILLON	NAIRA		Week 3	DILLON	ORLAGH
41			DILLON	NICOLE		Week 3	FERNE	SKY
42			DILLON	ORLAGH		Week 3	KENDRICK	LUAN
43			DILLON	RUQAYYAH		Week 3	LEXI-MAE	SKY
44			DILLON	SAFA		Week 3	NAIRA	SIYANA
45			DILLON	SIYANA		Week 3	ORLAGH	VERA
46			DILLON	SKY		Week 3	SKY	VERA
47			DILLON	STEPHANIE		Week 3	ALESSANDRO	LEXI-MAE
48			DILLON	VERA		Week 3	AMYRA	NAIRA
49			DILLON	WARREN		Week 3	DILLON	RUQAYYAH
50			FERNE	HUW		Week 3	FERNE	STEPHANIE
51			FERNE	KENDRICK		Week 3	KENDRICK	NAIRA
52			FERNE	LEXI-MAE		Week 3	LEXI-MAE	STEPHANIE
53			FERNE	LUAN		Week 3	NAIRA	SKY
54			FERNE	NAIRA		Week 3	ORLAGH	WARREN
55			FERNE	NICOLE		Week 3	SKY	WARREN
56			FERNE	ORLAGH		Week 4	ALESSANDRO	LUAN
57			FERNE	RUQAYYAH		Week 4	AMYRA	NICOLE
58			FERNE	SAFA		Week 4	DILLON	SAFA
59			FERNE	SIYANA		Week 4	FERNE	VERA
60			FERNE	SKY		Week 4	KENDRICK	NICOLE
61			FERNE	STEPHANIE		Week 4	LEXI-MAE	VERA
62			FERNE	VERA		Week 4	NAIRA	STEPHANIE
63			FERNE	WARREN		Week 4	RUQAYYAH	SAFA
64			HUW	KENDRICK		Week 4	STEPHANIE	VERA
65			HUW	LEXI-MAE		Week 4	ALESSANDRO	NAIRA
66			HUW	LUAN		Week 4	AMYRA	ORLAGH
67			HUW	NAIRA		Week 4	DILLON	SIYANA
68			HUW	NICOLE		Week 4	FERNE	WARREN
69			HUW	ORLAGH		Week 4	KENDRICK	ORLAGH
70			HUW	RUQAYYAH		Week 4	LEXI-MAE	WARREN
71			HUW	SAFA		Week 4	NAIRA	VERA
72			HUW	SIYANA		Week 4	RUQAYYAH	SIYANA
73			HUW	SKY		Week 4	STEPHANIE	WARREN
74			HUW	STEPHANIE		Week 5	ALESSANDRO	NICOLE
75			HUW	VERA		Week 5	AMYRA	RUQAYYAH
76			HUW	WARREN		Week 5	DILLON	SKY
77			KENDRICK	LEXI-MAE		Week 5	HUW	KENDRICK
78			KENDRICK	LUAN		Week 5	KENDRICK	RUQAYYAH
79			KENDRICK	NAIRA		Week 5	LUAN	NAIRA
80			KENDRICK	NICOLE		Week 5	NAIRA	WARREN
81			KENDRICK	ORLAGH		Week 5	RUQAYYAH	SKY
82			KENDRICK	RUQAYYAH		Week 5	VERA	WARREN
83			KENDRICK	SAFA		Week 5	ALESSANDRO	ORLAGH
84			KENDRICK	SIYANA		Week 5	AMYRA	SAFA
85			KENDRICK	SKY		Week 5	DILLON	STEPHANIE
86			KENDRICK	STEPHANIE		Week 5	HUW	LEXI-MAE
87			KENDRICK	VERA		Week 5	KENDRICK	SAFA
88			KENDRICK	WARREN		Week 5	LUAN	NICOLE
89			LEXI-MAE	LUAN		Week 5	NICOLE	ORLAGH
90			LEXI-MAE	NAIRA		Week 5	RUQAYYAH	STEPHANIE
91			LEXI-MAE	NICOLE		Week 5	ALESSANDRO	RUQAYYAH
92			LEXI-MAE	ORLAGH		Week 6	AMYRA	SIYANA
93			LEXI-MAE	RUQAYYAH		Week 6	DILLON	VERA
94			LEXI-MAE	SAFA		Week 6	HUW	LUAN
95			LEXI-MAE	SIYANA		Week 6	KENDRICK	SIYANA
96			LEXI-MAE	SKY		Week 6	LUAN	ORLAGH
97			LEXI-MAE	STEPHANIE		Week 6	NICOLE	RUQAYYAH
98			LEXI-MAE	VERA		Week 6	RUQAYYAH	VERA
99			LEXI-MAE	WARREN		Week 6	ALESSANDRO	SAFA
100			LUAN	NAIRA		Week 6	AMYRA	SKY
101			LUAN	NICOLE		Week 6	DILLON	WARREN
102			LUAN	ORLAGH		Week 6	HUW	NAIRA
103			LUAN	RUQAYYAH		Week 6	KENDRICK	SKY
104			LUAN	SAFA		Week 6	LUAN	RUQAYYAH
105			LUAN	SIYANA		Week 6	NICOLE	SAFA
106			LUAN	SKY		Week 6	RUQAYYAH	WARREN
107			LUAN	STEPHANIE		Week 6	ALESSANDRO	SIYANA
108			LUAN	VERA		Week 6	AMYRA	STEPHANIE
109			LUAN	WARREN		Week 6	FERNE	HUW
110			NAIRA	NICOLE		Week 7	HUW	NICOLE
111			NAIRA	ORLAGH		Week 7	KENDRICK	STEPHANIE
112			NAIRA	RUQAYYAH		Week 7	LUAN	SAFA
113			NAIRA	SAFA		Week 7	NICOLE	SIYANA
114			NAIRA	SIYANA		Week 7	SAFA	SIYANA
115			NAIRA	SKY		Week 7	ALESSANDRO	SKY
116			NAIRA	STEPHANIE		Week 7	AMYRA	VERA
117			NAIRA	VERA		Week 7	FERNE	KENDRICK
118			NAIRA	WARREN		Week 7	HUW	ORLAGH
119			NICOLE	ORLAGH		Week 7	KENDRICK	VERA
120			NICOLE	RUQAYYAH		Week 7	LUAN	SIYANA
121			NICOLE	SAFA		Week 7	NICOLE	SKY
122			NICOLE	SIYANA		Week 7	SAFA	SKY
123			NICOLE	SKY		Week 7	ALESSANDRO	STEPHANIE
124			NICOLE	STEPHANIE		Week 7	AMYRA	WARREN
125			NICOLE	VERA		Week 7	FERNE	LEXI-MAE
126			NICOLE	WARREN		Week 7	HUW	RUQAYYAH
127			ORLAGH	RUQAYYAH		Week 7	KENDRICK	WARREN
128			ORLAGH	SAFA		Week 8	LUAN	SKY
129			ORLAGH	SIYANA		Week 8	NICOLE	STEPHANIE
130			ORLAGH	SKY		Week 8	SAFA	STEPHANIE
131			ORLAGH	STEPHANIE		Week 8	ALESSANDRO	VERA
132			ORLAGH	VERA		Week 8	DILLON	FERNE
133			ORLAGH	WARREN		Week 8	FERNE	LUAN
134			RUQAYYAH	SAFA		Week 8	HUW	SAFA
135			RUQAYYAH	SIYANA		Week 8	LEXI-MAE	LUAN
136			RUQAYYAH	SKY		Week 8	LUAN	STEPHANIE
137			RUQAYYAH	STEPHANIE		Week 8	NICOLE	VERA
138			RUQAYYAH	VERA		Week 8	SAFA	VERA
139			RUQAYYAH	WARREN		Week 8	ALESSANDRO	WARREN
140			SAFA	SIYANA		Week 8	DILLON	HUW
141			SAFA	SKY		Week 8	FERNE	NAIRA
142			SAFA	STEPHANIE		Week 8	HUW	SIYANA
143			SAFA	VERA		Week 8	LEXI-MAE	NAIRA
144			SAFA	WARREN		Week 8	LUAN	VERA
145			SIYANA	SKY		Week 8	NICOLE	WARREN
146			SIYANA	STEPHANIE		Week 9	SAFA	WARREN
147			SIYANA	VERA		Week 9	AMYRA	DILLON
148			SIYANA	WARREN		Week 9	DILLON	KENDRICK
149			SKY	STEPHANIE		Week 9	FERNE	NICOLE
150			SKY	VERA		Week 9	HUW	SKY
151			SKY	WARREN		Week 9	LEXI-MAE	NICOLE
152			STEPHANIE	VERA		Week 9	LUAN	WARREN
153			STEPHANIE	WARREN		Week 9	ORLAGH	RUQAYYAH
154			VERA	WARREN		Week 9	SIYANA	SKY
Sheet1

Cell Formulas
Range	Formula
I2:J154	I2	=LET(s,G2:G19,r,TOROW(s),tc,UNIQUE(TOCOL(IF(s<>r,IF(s<r,s&"|"&r,r&"|"&s)))),f,FILTER(tc,tc<>FALSE),d,DROP(REDUCE("",f,LAMBDA(s,c,VSTACK(s,TEXTSPLIT(c,"|")))),1),d)
L2:L154	L2	="Week "&INT(SEQUENCE(ROWS(M2:M154),,0)/ROWS(G2:G19))+1
M2:N154	M2	=SORTBY(I2#,INDEX(G2:G19,MOD(SEQUENCE(ROWS(I2:I154),,0),ROWS(G2:G19))+1))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:J154	Expression	=MOD(ROW(),2)=0	text	YES
L2:N154	Expression	=MOD(ROW(),2)=0	text	YES

 

[felixstraube]

How about this?:
(I recycled a formula used for another request and added some functionality)
n is the number of students. In B2 you enter any date as a seed for the pseudo random number generation (for every date you get a different pair combination). And last in O6 you select the week, and you'll get the student names pairs for that week.
Column M is a table called "Students" where you can enter the names of your students.
After week 17, when all possible combinations are used, it will begin with the same sequence again. (if you want to have a differente sequence from after week 17 it is an easy fix. just let me know).


here is a working copy of the file:

Rotating partners.zip

(You can ignore/delete the other sheets in the book)

Rotating partners - (GolfPairs).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	n	18	(n even)
2	Seed	17/01/2024
3
4			Pairs
5			1	2	3	4	5	6	7	8	9		Students		Week		Week 1 Pairs
6	Weeks	1	4 - 17	10 - 11	6 - 15	3 - 18	5 - 16	9 - 12	7 - 14	1 - 2	8 - 13		StudentName01		1		StudentName04 - StudentName17
7		2	4 - 2	5 - 18	11 - 12	8 - 15	10 - 13	1 - 3	9 - 14	6 - 17	7 - 16		StudentName02				StudentName10 - StudentName11
8		3	6 - 2	8 - 17	5 - 3	10 - 15	7 - 18	11 - 14	12 - 13	1 - 4	9 - 16		StudentName03				StudentName06 - StudentName15
9		4	10 - 17	8 - 2	1 - 5	7 - 3	6 - 4	11 - 16	12 - 15	13 - 14	9 - 18		StudentName04				StudentName03 - StudentName18
10		5	7 - 5	8 - 4	14 - 15	13 - 16	12 - 17	1 - 6	9 - 3	11 - 18	10 - 2		StudentName05				StudentName05 - StudentName16
11		6	1 - 7	13 - 18	10 - 4	12 - 2	9 - 5	8 - 6	15 - 16	11 - 3	14 - 17		StudentName06				StudentName09 - StudentName12
12		7	15 - 18	1 - 8	13 - 3	14 - 2	12 - 4	9 - 7	16 - 17	11 - 5	10 - 6		StudentName07				StudentName07 - StudentName14
13		8	1 - 9	11 - 7	17 - 18	12 - 6	10 - 8	16 - 2	14 - 4	13 - 5	15 - 3		StudentName08				StudentName01 - StudentName02
14		9	15 - 5	13 - 7	1 - 10	12 - 8	17 - 3	16 - 4	11 - 9	14 - 6	18 - 2		StudentName09				StudentName08 - StudentName13
15		10	2 - 3	1 - 11	14 - 8	18 - 4	16 - 6	15 - 7	17 - 5	12 - 10	13 - 9		StudentName10
16		11	16 - 8	3 - 4	14 - 10	13 - 11	1 - 12	2 - 5	18 - 6	17 - 7	15 - 9		StudentName11
17		12	15 - 11	4 - 5	18 - 8	17 - 9	16 - 10	2 - 7	3 - 6	14 - 12	1 - 13		StudentName12
18		13	17 - 11	2 - 9	16 - 12	18 - 10	1 - 14	4 - 7	3 - 8	15 - 13	5 - 6		StudentName13
19		14	1 - 15	3 - 10	5 - 8	6 - 7	18 - 12	16 - 14	17 - 13	4 - 9	2 - 11		StudentName14
20		15	4 - 11	3 - 12	1 - 16	7 - 8	2 - 13	5 - 10	17 - 15	18 - 14	6 - 9		StudentName15
21		16	3 - 14	5 - 12	4 - 13	6 - 11	8 - 9	7 - 10	2 - 15	1 - 17	18 - 16		StudentName16
22		17	4 - 15	1 - 18	2 - 17	6 - 13	3 - 16	8 - 11	7 - 12	9 - 10	5 - 14		StudentName17
23		18	4 - 17	10 - 11	6 - 15	3 - 18	5 - 16	9 - 12	7 - 14	1 - 2	8 - 13		StudentName18
24		19	4 - 2	5 - 18	11 - 12	8 - 15	10 - 13	1 - 3	9 - 14	6 - 17	7 - 16
25		20	6 - 2	8 - 17	5 - 3	10 - 15	7 - 18	11 - 14	12 - 13	1 - 4	9 - 16
26		21	10 - 17	8 - 2	1 - 5	7 - 3	6 - 4	11 - 16	12 - 15	13 - 14	9 - 18
27		22	7 - 5	8 - 4	14 - 15	13 - 16	12 - 17	1 - 6	9 - 3	11 - 18	10 - 2
28		23	1 - 7	13 - 18	10 - 4	12 - 2	9 - 5	8 - 6	15 - 16	11 - 3	14 - 17
29		24	15 - 18	1 - 8	13 - 3	14 - 2	12 - 4	9 - 7	16 - 17	11 - 5	10 - 6
30		25	1 - 9	11 - 7	17 - 18	12 - 6	10 - 8	16 - 2	14 - 4	13 - 5	15 - 3
31		26	15 - 5	13 - 7	1 - 10	12 - 8	17 - 3	16 - 4	11 - 9	14 - 6	18 - 2
32		27	2 - 3	1 - 11	14 - 8	18 - 4	16 - 6	15 - 7	17 - 5	12 - 10	13 - 9
33		28	16 - 8	3 - 4	14 - 10	13 - 11	1 - 12	2 - 5	18 - 6	17 - 7	15 - 9
34		29	15 - 11	4 - 5	18 - 8	17 - 9	16 - 10	2 - 7	3 - 6	14 - 12	1 - 13
35		30	17 - 11	2 - 9	16 - 12	18 - 10	1 - 14	4 - 7	3 - 8	15 - 13	5 - 6
36
Rotating partners

Cell Formulas
Range	Formula
C5:K5	C5	=SEQUENCE(,B1/2)
B6:B35	B6	=SEQUENCE(30)
C6:K35	C6	=LET(studentPais, 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,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($B$2*(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), TAKE(VSTACK(studentPais,studentPais), 30) )
Q5	Q5	="Week " & O6&" Pairs"
Q6:Q14	Q6	=LET(w, O6, d, C6#, r, TOCOL(CHOOSEROWS(d, w)), sIndex1, TEXTSPLIT(r, " - "), sIndex2, BYROW(r, LAMBDA(x, TEXTAFTER(x, " - "))), INDEX(Students[Students], sIndex1) & " - " & INDEX(Students[Students], sIndex2) )
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
O6	List	=$B$6:$B$35

 

[upwordz]

Thank you both! I appreciate your quick and thorough solutions. I will look these over and let you know if I have questions.