Randomize

[hshayhorn]

I have two columns. Column A has a static list of names. Column B has the same names but I need to randomize that list so that those names are in a random order next to the names in column A but the names in column A and B can never match one another. This is to create a list of opponents. Is there an easy way to do this?

 

[Michael M]

You could sort col A in A-Z order then col B in Z-A order !!

 

[hshayhorn]

That doesn't randomize the list at all. I need to have random match ups. This needs to be down monthly so your solution is not workable at all.

 

[Michael M]

Ok, so try this then
Remove the names in Col B and insert the RAND() formula that column can then be hidden
then in Col C is the INDEX formula
Pressing F9 will reshuffle the names

Excel 2007
	A	B	C
1	w	0.45288	f
2	v	0.99335	w
3	n	0.24542	b
4	h	0.8936	n
5	g	0.87969	h
6	f	0.02975	x
7	e	0.26704	c
8	c	0.91241	v
9	b	0.84931	g
10	x	0.29331	e
Sheet1

Cell Formulas
Range	Formula
B1	=RAND()
B2	=RAND()
B3	=RAND()
B4	=RAND()
B5	=RAND()
B6	=RAND()
B7	=RAND()
B8	=RAND()
B9	=RAND()
B10	=RAND()
C1	=INDEX($A$1:$A$10,RANK(B1,$B$1:$B$10))
C2	=INDEX($A$1:$A$10,RANK(B2,$B$1:$B$10))
C3	=INDEX($A$1:$A$10,RANK(B3,$B$1:$B$10))
C4	=INDEX($A$1:$A$10,RANK(B4,$B$1:$B$10))
C5	=INDEX($A$1:$A$10,RANK(B5,$B$1:$B$10))
C6	=INDEX($A$1:$A$10,RANK(B6,$B$1:$B$10))
C7	=INDEX($A$1:$A$10,RANK(B7,$B$1:$B$10))
C8	=INDEX($A$1:$A$10,RANK(B8,$B$1:$B$10))
C9	=INDEX($A$1:$A$10,RANK(B9,$B$1:$B$10))
C10	=INDEX($A$1:$A$10,RANK(B10,$B$1:$B$10))

 

[hshayhorn]

Ok, so try this then
Remove the names in Col B and insert the RAND() formula that column can then be hidden
then in Col C is the INDEX formula
Pressing F9 will reshuffle the names
Excel 2007

	A	B	C
w	f
v	w
n	b
h	n
g	h
f	x
e	c
c	v
b	g
x	e

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]0.45288[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.99335[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.24542[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.8936[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0.87969[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0.02975[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0.26704[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0.91241[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0.84931[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0.29331[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B1[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C1[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B1,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B2[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C2[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B2,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B3[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C3[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B3,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B4[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C4[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B4,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B5[/TH]
[TD="align: left"]=RAND()
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C5[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B5,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B6[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C6[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B6,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B7[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C7[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B7,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B8[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C8[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B8,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B9[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C9[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B9,$B$1:$B$10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B10[/TH]
[TD="align: left"]=RAND()
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C10[/TH]
[TD="align: left"]=INDEX($A$1:$A$10,RANK(B10,$B$1:$B$10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That worked! Thank you!