Randomizer / Pairing

[juleskaynel]

I have a list of names and I want to randomly pair them in twos. How would I do this in Excel?

Example:
A1
A2
A3
A4
A5
A6
A7
A8

 

[DevelopedUnkown]

Hello, this seemed to work. How do I do this with 2 groups of people. Group A and Group B to be paired with one another with no duplicates?

 

[J.Ty.]

Sort one list A randomly: e.g. produce random numbers in the adjacent column, sort A by it, and then eliminate the column. Then pair first from A with first from B, second from A with second from B, etc.

J.Ty.

 

[J.Ty.]

First step:

Book1
	P	Q	R
1	0.251281194	A	a
2	0.532175884	B	b
3	0.096085385	C	c
4	0.646980925	D	d
5	0.278927558	E	e
6	0.364527085	F	f
7	0.893084416	G	g
Sheet3

Cell Formulas
Range	Formula
P1:P7	P1	=RAND()

Second step, after sorting:

Book1
	P	Q	R
1	0.312625451	C	a
2	0.018248263	A	b
3	0.212924523	E	c
4	0.198927373	F	d
5	0.203651345	B	e
6	0.677475488	D	f
7	0.286516701	G	g
Sheet3

Cell Formulas
Range	Formula
P1:P7	P1	=RAND()

Pairs are in consecutive rows of columns Q,R.

 

[J.Ty.]

Pairing from one list can be done in a similar way:
Sort list by random numbers in the adjacent column, then pair 1st with 2nd, 3rd with 4th, etc.
No need to use VBA or other high-tech methods.

J.Ty.

 

[DevelopedUnkown]

Using this, there must be a way...

Sub RandomPairing()

Dim Cnt As Long, RandomIndex As Long, Tmp As Variant, Arr As Variant
Randomize
Arr = Range("A2", Cells(Rows.Count, "A").End(xlUp))
For Cnt = UBound(Arr) To 1 Step -1
RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
Tmp = Arr(RandomIndex, 1)
Arr(RandomIndex, 1) = Arr(Cnt, 1)
Arr(Cnt, 1) = Tmp
Next
Range("G4").Resize(UBound(Arr)) = Arr
Range("G4").Offset(UBound(Arr) / 2).Resize(UBound(Arr)).Cut Range("H4")

End Sub