Extended Randomization

[mhale5379]

I am trying to take 40 names and pair up two a week for 24 weeks, not having any duplicates. I have tried using, my list of names in A1, filling up 40 cells, then =IF(A1="","",RAND()) in cell B1, copying it down then, =IF(ISERROR(INDEX(A:A,COUNTA(A:A)-RANK(B1,B:B))),"",INDEX(A:A,COUNTA(A:A)-RANK(B1,B:B))) in cell C1, copying it down. It works, but only for 2 additional weeks after my initial column, then it starts leaving 2 blank cells per column.


I'm a bit frustrated. I know it can be done but for the life of me can not firgure it out.


Your assistance would be greatly appreciated.


Thanks, Mike

 

[Luke M]

In B1, copied down as needed:

=RAND()


In C1:

=INDEX(A:A,MATCH(SMALL($B$1:$B$40,ROW(A1)),$B$1:$B$40,0))

In D1:

=INDEX(A:A,MATCH(LARGE($B$1:$B$40,ROW(A1)),$B$1:$B$40,0))


Copy down to row 24 to get 24 pairs of 2. As long as you have # of particpants is greater than number of weeks, this should work just fine.

 

[fred]

Hi Luke,


I tried your formula and on line 21 (assuming line 1 has the titles), the pair will be identical. that's C21 is James and D21 is James as well. I F2 the the worksheet and every time it will duplicate the same person's name twice on each of the column.


Frederick