What I am doing is a spreadsheet for the sport of Team Roping, where you have a header and a heeler. People come and enter and then randomly draw their partners. But sometimes one person will enter Heading and Heeling. For example George entered heading and heeling.

I have put a title on each column in Row 1.

Column A has all of the Headers names in it,

Column B has the following formula to randomly draw a Heeler, =INDEX($C$2:$C$14,RANK(D2,$D$2:$D$14))

Column C has the Heelers names in it

Column D has the following formula in it, =RAND()

I need to know if there is a way that I can put in the formula, so the same name does not appear in Column A row 2 Column B row 2, for example George cannot be drawn with George?

And how do I copy and paste just the values, after I have done the random draw in Column B, not the formula? I have tried the copy-paste special-values, but when I do that it randomly sorts them again, before it pastes.

If anyone has any suggestions on how to change the spreadsheet to achieve this please let me know.

Thanks.

I have put a title on each column in Row 1.

Column A has all of the Headers names in it,

Column B has the following formula to randomly draw a Heeler, =INDEX($C$2:$C$14,RANK(D2,$D$2:$D$14))

Column C has the Heelers names in it

Column D has the following formula in it, =RAND()

I need to know if there is a way that I can put in the formula, so the same name does not appear in Column A row 2 Column B row 2, for example George cannot be drawn with George?

And how do I copy and paste just the values, after I have done the random draw in Column B, not the formula? I have tried the copy-paste special-values, but when I do that it randomly sorts them again, before it pastes.

If anyone has any suggestions on how to change the spreadsheet to achieve this please let me know.

Thanks.

## Comment