Random name shuffle without repetitions

[secik]

Hi guys,

I've got four columns with numbers and names next to the numbers. It looks something like that:

cA cB cC cD
1 Anna 6 Frank
2 John 7 Marva
3 James 8 Alice
4 Craig 9 Keith
5 Paul 10 Mike

Now, what I need is to mix the names randomly with the different numbers, but any time I run the macro / script the same name cannot be with the same number until all the combinations have been used. Then it would reset ans start from the scratch.

Has anyone got any idea how to do something like that? Thanks in advance guys as I can't figure it out on my own at all...

 

[tusharm]

Suppose your data are in A:D starting with row 2.

1) Create a list of all valid combinations (25 in all):
In F2 enter the formula =INDEX($B$2:$B$6,INT((ROW()-ROW($F$2))/ROWS($D$2:$D$6))+1)
In G2 enter the formula =INDEX($D$2:$D$6,MOD(ROW()-ROW($F$2),ROWS($D$2:$D$6))+1)
Copy F2:G2 down to 3:27.

2) Create a random ranking order:
In H2 enter =RAND()
In I2 enter =RANK(H2,$H$2:$H$26)

Copy H2:I2 to 3:27.

3) Create a usable ranking order:
In K2:K27 enter the numbers 1, 2, ...25.
In L2 enter the formula =INDEX(F$2:F$26,MATCH($K2,$I$2:$I$26,0))
In M2 enter the formula =INDEX(G$2:G$26,MATCH($K2,$I$2:$I$26,0))

You have the desired random pairing in L:M The pairing will change whenever the worksheet recalculates.