I tried it and it still gives me duplicates. I'm not sure if I did the
=Rand() right though. I dragged the right corner down. I'm not sure what you
mean by the "paste special".

Also, do I need to "CSE" the formula? That's what I did and dragged down.

"joeu2004" wrote:

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list
in random order.

Does this do what you want?

Put =RAND() into K2:K22. I usually then copy-and-paste-special-value
so the random numbers do not keep changing.

Then put the following formula into the first cell of column A and
copy down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1)-1,0), $K$2:$K$22))

and put the following formula into the first cell of column C and copy
down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1),0), $K$2:$K$22))

Some notes:

1. The number of random numbers, K2:K22, should be the same as the
number of names in column -- 21 items in B3:B23.

2. You can have only 10 pairs in 21 names; one name is not used. Is
that really your intent?


----- original posting -----

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave