Macro to randomise names

[jwoww]

I have a list of competitors that need to be randomly paired up on another sheet to allocate the initial pairings randomly with each other regardless of any other factors.

I would then need a separate function it to be able to randomly pair the names on another sheet based on names that belong to the same group as each other ie. only pair people from group a with people from group a, people from group b with group b etc

Can anyone help suggest where to look to find out how to do this?

 

[jwoww]

Competition.xlsx
	A	B	C	D	E	F	G	H	I
2	Mohamad Al	0.83041	1000	1	16.00	1016	5	1	Normal Class
3	Wolfe Glick	0.87182	1000	1	1.00	1001	18	1	Normal Class
4	Evgeny Bareev	0.54959	1000	1	0.00	1000	23	0	Great Class
5	Dragan Solak	0.67759	1000	1	0.00	1000	23	0	Great Class
6	Frank De	0.75419	1000	1	0.00	1000	23	0	Normal Class
7	Dustin Zimmerman	0.91446	1000	1	0.00	1000	23	0	Normal Class
8	Iván Morovic	0.91252	1000	1	0.00	1000	23	0	Normal Class
9	Toler Webb	0.18382	1000	1	0.00	1000	23	0	Master Class
10	Li Shilong	0.41998	1000	1	0.00	1000	23	0	Ultra Class
11	Ultimo	0.07758	1000	1	0.00	1000	23	0	Master Class
12	Jeff Kolenc	0.45709	1000	1	0.00	1000	23	0	Ultra Class
13	Alessio Parcianello	0.86762	1000	1	0.00	1000	23	0	Normal Class
14	Karuki Ishimaru	0.28489	1000	1	0.00	1000	23	0	Ultra Class
15	Martyn Kravtsiv	0.14885	1000	1	0.00	1000	23	0	Master Class
16	Brendan Zheng	0.53708	1000	1	0.00	1000	23	0	Great Class
17	Ilia Iljiushenok,	0.55250	1000	1	0.00	1000	23	0	Great Class
18	Yusup Atabayev,	0.52228	1000	1	0.00	1000	23	0	Great Class
19	Hayden Caneron-Jacobus	0.19725	1000	1	0.00	1000	23	0	Master Class
20	Kotone Yasue	0.94472	1000	1	0.00	1000	23	0	Normal Class
21	Sergey Kudrin	0.82499	1000	1	0.00	1000	23	0	Normal Class
22	Nicolas Fotheringham	0.14036	1000	1	0.00	1000	23	0	Master Class
23	Sergio Barrientos	0.86856	1000	1	0.00	1000	23	0	Normal Class
Competitors

Cell Formulas
Range	Formula
D2:D23,G2:G23	D2	=RANK(C2,C$2:C$1213)
E2:E23	E2	=SUM(SUMIFS(Matches!E:E,Matches!B:B,A2),SUMIFS(Matches!L:L,Matches!I:I,A2))
F2:F23	F2	=E2+C2
H2:H23	H2	=SUM(SUMPRODUCT((Matches!B$3:B$31=A2)*(Matches!G$3:G$31>Matches!H$3:H$31)),SUMPRODUCT((Matches!I$3:I$31=A2)*(Matches!G$3:G$31< Matches!H$3:H$31)))
I2:I23	I2	=CHOOSE(ROUNDUP(RANK(B2,B$2:B$1048576)/$K$2,0),"Normal Class","Great Class","Ultra Class","Master Class")
B2:B23	B2	=RAND()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Cell Value	duplicates	text	NO
H:H	Cell Value	duplicates	text	NO
A2:A1087,A1769:A1048576	Cell Value	duplicates	text	NO
A2:A100	Cell Value	duplicates	text	NO
A2:A100	Cell Value	duplicates	text	NO

 

[StephenCrump]

A formula approach:

	A	B	C	D	E	F	G	H	I
1								Group No	1
2	Names1	Group size (N_1)	2		Names2	Group2		Group size (N_2)	2
3	A	B	H		K	1		P	R
4	B	I	E		L	2		L	O
5	C	J	A		M	1		M	Q
6	D	F	D		N	2		K	N
7	E	C	G		O	1
8	F				P	1
9	G				Q	2
10	H				R	1
11	I				S	2
12	J				T	2
13					U	1
14					V	2
15					W	1
16					X	2
17					Y	2
18					Z	1
Sheet1

Cell Formulas
Range	Formula
B3:C7	B3	=LET(c,ROWS(Names1),INDEX(Names1,INDEX(SORTBY(SEQUENCE(c), RANDARRAY(c)),SEQUENCE(c/N_1,N_1))))
H3:I6	H3	=LET(c,COUNTIF(Group2,I1),INDEX(Names2,INDEX(SORTBY(SEQUENCE(c), RANDARRAY(c)),SEQUENCE(c/N_2,N_2))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Group2	=Sheet1!$F$3:$F$18	H3
N_1	=Sheet1!$C$2	B3
N_2	=Sheet1!$I$2	H3
Names1	=Sheet1!$A$3:$A$12	B3
Names2	=Sheet1!$E$3:$E$18	H3

Late edit: H3 should be: =LET(c,COUNTIF(Group2,I1),INDEX(FILTER(Names2,Group2=I1),INDEX(SORTBY(SEQUENCE(c), RANDARRAY(c)),SEQUENCE(c/N_2,N_2))))

 

[jwoww]

Hi,

I dont quite understand your formula that you've listed. Are you able to explain it a little bit please?

 

[StephenCrump]

Here's the first formula. Is it doing what you want?

The second is similar, but first we need to filter by group: The formula in H3 above should have been:

H3:=LET(c,COUNTIF(Group2,I1),INDEX(FILTER(Names2,Group2=I1),INDEX(SORTBY(SEQUENCE(c), RANDARRAY(c)),SEQUENCE(c/N_2,N_2))))

	A	B	C
1
2	Names1	Group size (N_1)	2
3	A	F	J
4	B	I	E
5	C	B	A
6	D	G	D
7	E	C	H
8	F
9	G
10	H	Step 1
11	I	Random 1-10
12	J	7
13		6
14		10
15		5
16		9
17		4
18		1
19		3
20		2
21		8
22
23		Step 2
24		Group into pairs
25		7	6
26		10	5
27		9	4
28		1	3
29		2	8
30
31		Step 3
32		Attach names
33		G	F
34		J	E
35		I	D
36		A	C
37		B	H
38
39		Step 4
40		Put it together
41		D	I
42		A	C
43		H	J
44		E	G
45		F	B
46
47		Step 5
48		Parameterise
49		H	G
50		A	J
51		C	E
52		I	F
53		B	D
Sheet1

Cell Formulas
Range	Formula
B3:C7	B3	=LET(c,ROWS(Names1),INDEX(Names1,INDEX(SORTBY(SEQUENCE(c), RANDARRAY(c)),SEQUENCE(c/N_1,N_1))))
B12:B21	B12	=SORTBY(SEQUENCE(10), RANDARRAY(10))
B25:C29	B25	=INDEX(B12#,SEQUENCE(5,2))
B33:C37	B33	=INDEX(Names1,B25#)
B41:C45	B41	=INDEX(Names1,INDEX(SORTBY(SEQUENCE(10), RANDARRAY(10)),SEQUENCE(5,2)))
B49:C53	B49	=INDEX(Names1,INDEX(SORTBY(SEQUENCE(ROWS(Names1)), RANDARRAY(ROWS(Names1))),SEQUENCE(ROWS(Names1)/N_1,N_1)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N_1	=Sheet1!$C$2	B3, B49
Names1	=Sheet1!$A$3:$A$12	B3, B41, B49, B33

 

[jwoww]

Hi,

Thanks for that. I'm not sure if its just because i've been really busy at work and am super tired but it still doesn't make that much sense to me. I think i figured out an alternative that does most of what i want.

Thanks again

 

[StephenCrump]

I may have misinterpreted what you were asking.

It would be good to see your solution if you care to post it - it may help others looking to create randomised pairings.