Randomizing a list of words in excel

walls07

New Member
Joined
Mar 7, 2008
Messages
26
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Stephen

Give this a try:

Code:
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

New Member
Joined
Mar 7, 2008
Messages
26
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Mar 7, 2008
Messages
26

ADVERTISEMENT

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

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Apr 3, 2010
Messages
1
Hi Stephen

Give this a try:

Code:
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

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
An easier one:
Code:
Sub RandomizeOrder()
[b1].Resize([a65536].End(3).Row) = "=rand()"
[a:b].Sort [b1]
[b:b] = ""
End Sub
Regards
Northwolves
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,268
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top