Randomizing a list of words in excel

[walls07]

I have list of 1,000 words and I would like to mix them up in a random order.
Does anyone know if these is any code I can enter into excel to perform this function?

Thank you
Stephan

 

[Richard Schollar]

Hi Stephen

Give this a try:

Sub RandomizeOrder()
Dim v, temp
Dim i As Long, el As Long
Randomize
v = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 1 To UBound(v, 1)
    el = Int(Rnd * UBound(v, 1)) + 1
    temp = v(el, 1)
    v(el, 1) = v(i, 1)
    v(i, 1) = temp
Next
Range("A1").Resize(UBound(v, 1)) = v
End Sub

 

[walls07]

Hi Richard,

Thanks for your quick reply. Where do I need to enter that code? All my words are listed 1-1000 in column A.

Stephan

 

[Richard Schollar]

It needs to go into a standard module in your workbook containing the words:

1. With the words visible on in your Excel window, open up the VBE with Alt+F11
2. Insert a standard module with Alt+I then Alt+M
3. Paste the code into the code module which will open on your right
4. Back in Excel, run the macro by Tools>Macro>Macros and select it from the list and hit Run.

Note that I assumed that your words were in column A starting from A1.

 

[walls07]

I understand now. Thanks for your help, much appreciated.

 

[Weaver]

alternatively, if your words are in column A, in column B place the formula

=rand()

copy down then sort the range on column B

 

[BRDParker]

Hi Stephen

Give this a try:

Sub RandomizeOrder()
Dim v, temp
Dim i As Long, el As Long
Randomize
v = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 1 To UBound(v, 1)
    el = Int(Rnd * UBound(v, 1)) + 1
    temp = v(el, 1)
    v(el, 1) = v(i, 1)
    v(i, 1) = temp
Next
Range("A1").Resize(UBound(v, 1)) = v
End Sub

Contortionist, you have saved me so much hassle, thank you very much! I have been scouring the forums trying to find this.

 

[northwolves]

An easier one:

Sub RandomizeOrder()
[b1].Resize([a65536].End(3).Row) = "=rand()"
[a:b].Sort [b1]
[b:b] = ""
End Sub

Regards
Northwolves