Need Help, need a code to randomly mix cells

[Shawnathon]

Need Help, need a code to randomly mix cells

I have a list of 5000 names and would like to mix up the names and place them into another column or page.

Is there a code that would make the mix of the names different every time? Do not want to have same outcome every time. Please help, thanks

Shawn

 

[energman58]

How I would do this is to insert a new column next to your original data with =Rand() in it then record a macro to copy your two columns and paste-special-values them to a new worksheet. Select all the data on this new sheet and sort it by your random number column using the name column as the second criteria. Delete your column of random numbers. Stop recording.

You can then use the macro to generate a new list in your second sheet - the list will be in a different order each time you run the macro. The only other thing I would do is maybe force a recalculation at the begining of the macro to make sure that the RAND function recalculates before you copy your data to the second sheet you can do this by hitting F9 when you record your macro.

 

[Shawnathon]

DO I need to put =Rand() in all the cells next to each name? When I enter (=Rand()) to the right of the first row i get a number like this - <table x:str="" style="border-collapse: collapse; width: 245pt;" width="327" border="0" cellpadding="0" cellspacing="0"><col style="width: 245pt;" width="327"><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 245pt;" x:num="0.54076310172524344" x:fmla="=RAND()" width="327" align="right" height="17">0.540763102</td> </tr></table>Dose that look like I'm on the right track?

How do I find the special values? Do not know how to sort using the second criteria, when I go to sort I do not see that option. Sorry I'm sort of new at doing macros but know how to record them. Just need to clarify the steps with you, thanks for your help.

 

[energman58]

Yes thats right put rand() in your first cell and copy it down to the bottom of your names list - that will put a series of random values between 0 and 1 in that column next to the names

Then start the macro recorder - give it a name

Then copy the whole range of names and random numbers, select your spare sheet and click paste special - values - Ok

That will put a copy of the names and the associated random numbers as fixed values in that sheet

Then select all the data in your second sheet where you just pasted the data

Then you need to sort the data (in Excel 2007 you click data sort which gives you a dialog box in there where it says "sort by" select the column with the numbers and then click add a level and select the names column in the "sort by" box for this - in the unlikley event that tehre are any ties (it doesnt matter which way you sort them as we are looking for a random order) and then click OK - but it is basically similar in all versions). This will sort your list based on the random numbers using teh names to break any ties.

Then you can delete the column with the numbers in your second sheet as you no longer need it (leave the one with Rand() in your first sheet alone) and stop the macro recorder. This will leave you with a random list of all 5000 names on your second sheet but preserve the original list on your first sheet

Then whenever you need to randomize your names you run the macro and the list on the second sheet will change - you can then use it however you want in formulas etc.

HTH - good luck

 

[Shawnathon]

OK, I found the Past Special Values by right clicking after I copy.

Still not sure about the =Rand() code if I'm inserting it correctly.

Thanks

 

[energman58]

Sounds like you are on the right track - just put =Rand() in the first cell and copy it and paste it to a range next to your list of names - it doesnt need any other values. That should give you a different random number in each cell which is what we want.

Then proceed as I suggest above - maybe if you have a play with sorting some data on another smaller workbook you will see how it works.

 

[Shawnathon]

OK, I got it! Thanks for your help on this! Appreciate it alot!

I have another macro that I need help modifying, should I post a new thread or can You help me with that one too, thanks!

Shawn

 

[mirabeau]

Need Help, need a code to randomly mix cells

I have a list of 5000 names and would like to mix up the names and place them into another column or page.

Is there a code that would make the mix of the names different every time? Do not want to have same outcome every time. Please help, thanks

Shawn

Hi Shawn,

Put some names (5000 if you like) down column A starting from the first cell.

Then run this code several times and see if it's your sort of thing.

Sub randnames()
Dim a As Variant, n As Long, i As Long
Dim x As Variant, u() As Variant
a = [a1].CurrentRegion.Resize(, 1)
n = UBound(a)
ReDim u(1 To n, 1 To 1)
For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    u(i, 1) = a(x, 1)
    a(x, 1) = a(i, 1)
Next i
[c1].Resize(n) = u
[c1].Resize(n).Insert xlToRight
End Sub

 

[energman58]

Probably best to post another thread - I am off home now and someone in a different time zone may pick it up before tomorrow!

 

[Shawnathon]

Thanks Marabeau!

That code does work but does not randomize it different every time, it does the same exact random of the names, I tried it a few times on the same list and got the same results. Thanks for the help though, appreciate it, I did get energman58's code to work perfect! Thanks to both of you on the help in this forum! It rocks! I have some other XL questions comming up soon, thanks!

Shawn