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
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

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

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

<tbody>
</tbody>

That worked! Thank you!