How to shuffle randomly 1

[Ramy27]

I have an array of about 50 names in Excel column A. I want a program/ macro that shuffles these names in a completely random way and puts them in column B.

1) the names shouldn't be repeated.
2) one person shouldn't have favour compared to the other- i.e, the shuffling should be RANDOM.

Any idea?



---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[xlbo]

How about checking out the RAND() function - that should give you your random numbers - then just sort based on that

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Ramy27]

i have check that, but it can repeat a number and therefore the name.

---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[earthandfire]

Since you are only looking for 50 numbers, when a number is generated, check to see if it has been used, if it has generate a new one, otherwise use it.

 

[PHV]

A starting point (copy randomly A1:A50 to B1:B50):
Sub Shuffle()
Dim a(49) As Boolean, i As Long, r As Long
For i = 1 To 50
r = Int(50 * Rnd)
If a(r) Then
i = i - 1
Else
Cells(i, 2) = Cells(1 + r, 1)
a(r) = True
End If
Next i
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Well, yes, technically, it can repeat but the chances of doing so to 6 decimal places are pretty remote.....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[earthandfire]

PHV, whilst you solution works, it would not be usable in some other programming environments which prohibit changing the looping variable within a For loop. A more general solution would be to use a While loop.

 

[PHV]

earthandfire, aren't we in the VBA forum ?

 

[earthandfire]

I'm totally lost - just following you around

 

[earthandfire]

Seriously, though, I'm just wandering through the fora / forums where I think I might be of some help.

 

[Ramy27]

Thanks very much.

---------------------------------
"Good things are the enemies of the best things"
---------------------------------