Random Team Pairings

[excelbytes]

I have two groups #1 & #2. I need to create random pairs between the two groups, however I need to make sure that the pairings from Group #1 and Group #2 are not on the same team. Is there a way to do this?

Team Pairing.xlsm
	A	B	C	D	E
1	Group #1			Group #2
2	Number	Team		Number	Team
3	1	A		70	A
4	2	A		71	A
5	3	A		72	A
6	4	A		73	A
7	5	A		74	A
8	6	A		75	A
9	7	B		76	B
10	8	B		77	B
11	9	B		78	B
12	10	C		79	C
13	11	C		80	C
14	12	C		81	C
15	13	D		82	D
16	14	C		83	C
17	15	C		84	C
18	16	E		85	E
19	17	E		86	E
20	18	E		87	E
21	19	E		88	E
22	20	E		89	E
23	21	E		90	E
24	22	F		91	F
25	23	F		92	F
26	24	F		93	F
27	25	G		94	G
28	26	G		95	G
29	27	G		96	G
30	28	H		97	H
31	29	H		98	H
32	30	H		99	H
33	31	I		100	I
34	32	I		101	I
35	33	I		102	I
36	34	J		103	J
37	35	J		104	J
38	36	J		105	J
39	37	J		106	J
40	38	J		107	J
41	39	J		108	J
42	40	K		109	K
43	41	K		110	K
44	42	K		111	K
45	43	L		112	L
46	44	L		113	L
47	45	L		114	L
48	46	M		115	M
49	47	M		116	M
50	48	M		117	M
51	49	N		118	N
52	50	N		119	N
53	51	N		120	N
54	52	N		121	N
55	53	N		122	N
56	54	N		123	N
57	55	O		124	O
58	56	O		125	O
59	57	O		126	O
60	58	O		127	O
61	59	O		128	O
62	60	O		129	O
63	61	P		130	P
64	62	P		131	P
65	63	P		132	P
66	64	D		133	D
67	65	D		134	D
68	66	D		135	D
69	67	Q		136	Q
70	68	Q		137	Q
71	69	Q		138	Q
Sheet1

 

[Marc L]

I need to create random pairs between the two groups,

But how many ?!​

​

Any expected result ?​

​

Via a VBA procedure ?​

 

[excelbytes]

But how many ?! There are 69 pairs. I need to pair 1-69 with 70-138 randomly for team matchups, and will need to do this every week with a different pairing.

Any expected result ? See above.

Via a VBA procedure ? VBA would be fine. I would also be interested if there was a formula way to do this, but if that's not possible, then VBA.

 

[Eric W]

How about:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Group #1			Group #2			Pairs
2	Number	Team		Number	Team		Number	Number
3	1	A		70	A		32	93
4	2	A		71	A		63	111
5	3	A		72	A		7	74
6	4	A		73	A		54	109
7	5	A		74	A		22	89
8	6	A		75	A		65	71
9	7	B		76	B		16	138
10	8	B		77	B		45	136
11	9	B		78	B		35	117
12	10	C		79	C		12	128
13	11	C		80	C		19	76
14	12	C		81	C		43	137
15	13	D		82	D		46	120
16	14	C		83	C		50	96
17	15	C		84	C		69	85
18	16	E		85	E		25	104
19	17	E		86	E		21	99
20	18	E		87	E		1	98
21	19	E		88	E		36	131
22	20	E		89	E		48	108
23	21	E		90	E		33	87
24	22	F		91	F		40	105
25	23	F		92	F		68	106
26	24	F		93	F		20	121
27	25	G		94	G		62	86
28	26	G		95	G		34	92
29	27	G		96	G		6	122
30	28	H		97	H		51	97
31	29	H		98	H		5	114
32	30	H		99	H		49	101
33	31	I		100	I		37	134
34	32	I		101	I		11	70
35	33	I		102	I		26	133
36	34	J		103	J		58	119
37	35	J		104	J		42	132
38	36	J		105	J		28	107
39	37	J		106	J		3	129
40	38	J		107	J		66	82
41	39	J		108	J		23	130
42	40	K		109	K		14	112
43	41	K		110	K		10	94
44	42	K		111	K		60	79
45	43	L		112	L		8	103
46	44	L		113	L		39	72
47	45	L		114	L		38	90
48	46	M		115	M		61	100
49	47	M		116	M		53	135
50	48	M		117	M		31	110
51	49	N		118	N		27	81
52	50	N		119	N		67	113
53	51	N		120	N		17	91
54	52	N		121	N		29	77
55	53	N		122	N		13	124
56	54	N		123	N		9	116
57	55	O		124	O		2	123
58	56	O		125	O		59	118
59	57	O		126	O		52	88
60	58	O		127	O		47	84
61	59	O		128	O		15	80
62	60	O		129	O		24	73
63	61	P		130	P		56	126
64	62	P		131	P		57	102
65	63	P		132	P		18	115
66	64	D		133	D		4	83
67	65	D		134	D		55	127
68	66	D		135	D		41	95
69	67	Q		136	Q		64	125
70	68	Q		137	Q		30	78
71	69	Q		138	Q		44	75
Sheet13

Cell Formulas
Range	Formula
G3:H71	G3	=CHOOSE({1,2},SORTBY(A3:A71,RANDARRAY(69)),SORTBY(D3:D71,RANDARRAY(69)))
Dynamic array formulas.

 

[excelbytes]

The challenge with this is the comment that I made "I need to make sure that the pairings from Group #1 and Group #2 are not on the same team". Your solution has several matches of the teams.

 

[Eric W]

I didn't understand that requirement. Give this a shot:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Group #1			Group #2			Pairs
2	Number	Team		Number	Team		Number	Number		Pairs
3	1	A		70	A		1	124		69
4	2	A		71	A		2	83
5	3	A		72	A		3	112
6	4	A		73	A		4	82
7	5	A		74	A		5	110
8	6	A		75	A		6	126
9	7	B		76	B		7	87
10	8	B		77	B		8	128
11	9	B		78	B		9	81
12	10	C		79	C		10	101
13	11	C		80	C		11	129
14	12	C		81	C		12	130
15	13	D		82	D		13	70
16	14	C		83	C		14	123
17	15	C		84	C		15	78
18	16	E		85	E		16	73
19	17	E		86	E		17	125
20	18	E		87	E		18	116
21	19	E		88	E		19	131
22	20	E		89	E		20	137
23	21	E		90	E		21	114
24	22	F		91	F		22	135
25	23	F		92	F		23	99
26	24	F		93	F		24	74
27	25	G		94	G		25	90
28	26	G		95	G		26	115
29	27	G		96	G		27	117
30	28	H		97	H		28	96
31	29	H		98	H		29	89
32	30	H		99	H		30	118
33	31	I		100	I		31	97
34	32	I		101	I		32	122
35	33	I		102	I		33	133
36	34	J		103	J		34	76
37	35	J		104	J		35	132
38	36	J		105	J		36	71
39	37	J		106	J		37	80
40	38	J		107	J		38	95
41	39	J		108	J		39	72
42	40	K		109	K		40	94
43	41	K		110	K		41	91
44	42	K		111	K		42	113
45	43	L		112	L		43	86
46	44	L		113	L		44	75
47	45	L		114	L		45	92
48	46	M		115	M		46	79
49	47	M		116	M		47	127
50	48	M		117	M		48	109
51	49	N		118	N		49	93
52	50	N		119	N		50	100
53	51	N		120	N		51	77
54	52	N		121	N		52	107
55	53	N		122	N		53	104
56	54	N		123	N		54	103
57	55	O		124	O		55	108
58	56	O		125	O		56	119
59	57	O		126	O		57	138
60	58	O		127	O		58	85
61	59	O		128	O		59	98
62	60	O		129	O		60	106
63	61	P		130	P		61	120
64	62	P		131	P		62	88
65	63	P		132	P		63	84
66	64	D		133	D		64	111
67	65	D		134	D		65	105
68	66	D		135	D		66	136
69	67	Q		136	Q		67	121
70	68	Q		137	Q		68	134
71	69	Q		138	Q		69	102
Sheet3

Cell Formulas
Range	Formula
J3	J3	=SUM(COUNTIF(D3:D71,H3:H71))
H3:H71	H3	=SMALL(IF((COUNTIF(H$2:H2,D$3:D$71)=0)*(B3<>E$3:E$71),D$3:D$71),RANDBETWEEN(1,SUM((COUNTIF(H$2:H2,D$3:D$71)=0)*(B3<>E$3:E$71))))
Press CTRL+SHIFT+ENTER to enter array formulas.

There is a chance that the last pair can't be assigned, due to how the algorithm works. I added the J3 formula to check that. If the number isn't 69, just press F9 again.

 

[Akuini]

Another option with VBA:

Sub a1181792a()
Dim i As Long, j As Long, n As Long, qq As Long
Dim c As Range
Dim tx As String
Dim va
Dim d As Object
Dim e As Object


va = Range("A3", Cells(Rows.Count, "B").End(xlUp))
vb = Range("D3", Cells(Rows.Count, "E").End(xlUp))

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
Set e = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    For i = 1 To UBound(va, 1)
        d(va(i, 1)) = va(i, 2)
        e(vb(i, 1)) = vb(i, 2)
    Next
    
ReDim vc(1 To UBound(va, 1), 1 To 2)

For Each x In d.Keys
    Randomize:    qq = 0
    Do While d.Count > 0
    a = WorksheetFunction.RandBetween(0, e.Count - 1)
    If d(x) <> e.Items()(a) Then
        j = j + 1
        vc(j, 1) = x
        vc(j, 2) = e.Keys()(a)
        d.Remove x
        e.Remove e.Keys()(a)
        Exit Do
    End If
    qq = qq + 1: If qq > 2000 Then MsgBox "Too many loops, restart the Sub": Exit Sub
    Loop
Next

'RESULT
Range("G3").Resize(UBound(vc, 1), 2) = vc
End Sub

 

[StephenCrump]

I need to pair 1-69 with 70-138 randomly for team matchups, and will need to do this every week with a different pairing.

Just an observation, as I'm coming in late.

If Group 1 Member #1 was paired with Group 2 Member #74, say, two or even three weeks in a row, that would certainly be random. But probably not an acceptable solution?

This sounds more like the Social Golfer Problem, i.e. you want different (rather than random) pairings each week?

 

[excelbytes]

I didn't understand that requirement. Give this a shot:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Group #1			Group #2			Pairs
2	Number	Team		Number	Team		Number	Number		Pairs
3	1	A		70	A		1	124		69
4	2	A		71	A		2	83
5	3	A		72	A		3	112
6	4	A		73	A		4	82
7	5	A		74	A		5	110
8	6	A		75	A		6	126
9	7	B		76	B		7	87
10	8	B		77	B		8	128
11	9	B		78	B		9	81
12	10	C		79	C		10	101
13	11	C		80	C		11	129
14	12	C		81	C		12	130
15	13	D		82	D		13	70
16	14	C		83	C		14	123
17	15	C		84	C		15	78
18	16	E		85	E		16	73
19	17	E		86	E		17	125
20	18	E		87	E		18	116
21	19	E		88	E		19	131
22	20	E		89	E		20	137
23	21	E		90	E		21	114
24	22	F		91	F		22	135
25	23	F		92	F		23	99
26	24	F		93	F		24	74
27	25	G		94	G		25	90
28	26	G		95	G		26	115
29	27	G		96	G		27	117
30	28	H		97	H		28	96
31	29	H		98	H		29	89
32	30	H		99	H		30	118
33	31	I		100	I		31	97
34	32	I		101	I		32	122
35	33	I		102	I		33	133
36	34	J		103	J		34	76
37	35	J		104	J		35	132
38	36	J		105	J		36	71
39	37	J		106	J		37	80
40	38	J		107	J		38	95
41	39	J		108	J		39	72
42	40	K		109	K		40	94
43	41	K		110	K		41	91
44	42	K		111	K		42	113
45	43	L		112	L		43	86
46	44	L		113	L		44	75
47	45	L		114	L		45	92
48	46	M		115	M		46	79
49	47	M		116	M		47	127
50	48	M		117	M		48	109
51	49	N		118	N		49	93
52	50	N		119	N		50	100
53	51	N		120	N		51	77
54	52	N		121	N		52	107
55	53	N		122	N		53	104
56	54	N		123	N		54	103
57	55	O		124	O		55	108
58	56	O		125	O		56	119
59	57	O		126	O		57	138
60	58	O		127	O		58	85
61	59	O		128	O		59	98
62	60	O		129	O		60	106
63	61	P		130	P		61	120
64	62	P		131	P		62	88
65	63	P		132	P		63	84
66	64	D		133	D		64	111
67	65	D		134	D		65	105
68	66	D		135	D		66	136
69	67	Q		136	Q		67	121
70	68	Q		137	Q		68	134
71	69	Q		138	Q		69	102
Sheet3

Cell Formulas
Range	Formula
J3	J3	=SUM(COUNTIF(D3:D71,H3:H71))
H3:H71	H3	=SMALL(IF((COUNTIF(H$2:H2,D$3:D$71)=0)*(B3<>E$3:E$71),D$3:D$71),RANDBETWEEN(1,SUM((COUNTIF(H$2:H2,D$3:D$71)=0)*(B3<>E$3:E$71))))
Press CTRL+SHIFT+ENTER to enter array formulas.

There is a chance that the last pair can't be assigned, due to how the algorithm works. I added the J3 formula to check that. If the number isn't 69, just press F9 again.

That works, thanks!

 

[Eric W]

Glad we could help!