×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# how to randomize numbers in Excel?2

## how to randomize numbers in Excel?

(OP)
I need to randomize the numbers 1-60 so they are completely shuffled up. I tried using RANDBETWEEN(1,60) to get a column full of the 60 numbers, but found that even though there were 60 random numbers, not all numbers between 1 and 60 were included, and there was some repitition.
Can anything in Excel help me? Or any of the other Office programs?

### RE: how to randomize numbers in Excel?

2
A simple way would be to construct 2 columns, the first with numbers 1-60, the second with the formula =RAND() copied for all 60 rows.

Sort the table based on the second column and you would have a randomized list.

### RE: how to randomize numbers in Excel?

Hi,
I don't think that JVF's solution works -- I don't think there's a way to do it in with a spreadsheet function,

cuz you hafta keep track o' wuts bin slekted

Try this one.  Writes the list in col A...

Sub Randomize(nFrom As Integer, nThru As Integer)
Dim iRand1() As Integer, nNbr As Integer
Dim iRand2() As Integer
'load Rand1 with nbrs, clear Rand2
ReDim iRand1(nThru - nFrom + 1)
ReDim iRand2(nThru - nFrom + 1)

For i = nFrom To nThru
iRand1(i) = (i)
iRand2(i) = 0
Next

nThru2 = nThru
For i = nFrom To nThru
nNbr = Rnd * (nThru2 - nFrom) + nFrom
iRand2(i) = iRand1(nNbr)
' reorder Rand1
k = 0
For j = nFrom To nThru2
If j <> nNbr Then
iRand1(nFrom + k) = iRand1(j)
k = k + 1
End If
Next j
ReDim Preserve iRand1(k)
nThru2 = nThru2 - 1
Next

For i = nFrom To nThru
Cells(i, 1).Value = iRand2(i)
Next
End Sub

Hope this works 4 U

### RE: how to randomize numbers in Excel?

There were some logic errors in Randomize above.

Oh, by the way, you must call Randomize with two arguments in another procedure...

Sub main()
Randomize 1, 60
End Sub

Sub Randomize(nFrom As Integer, nThru As Integer)
Dim iRand1() As Integer, nNbr As Integer
Dim iRand2() As Integer
'load Rand1 with nbrs, clear Rand2
ReDim iRand1(nFrom To nThru)
ReDim iRand2(nFrom To nThru)

For i = nFrom To nThru
iRand1(i) = (i)
iRand2(i) = 0
Next

nThru2 = nThru
For i = nFrom To nThru
nNbr = Rnd * (nThru2 - nFrom) + nFrom
iRand2(i) = iRand1(nNbr)
' reorder Rand1
k = 0
For j = nFrom To nThru2
If j <> nNbr Then
iRand1(nFrom + k) = iRand1(j)
k = k + 1
End If
Next j
nThru2 = nThru2 - 1
If nThru2 >= nFrom Then _
ReDim Preserve iRand1(nFrom To nThru2)
Next

For i = nFrom To nThru
Cells(i, 1).Value = iRand2(i)
Next
End Sub

Sorry that I jumped the gun on the previous code.

### RE: how to randomize numbers in Excel?

JVF's method is simple and effective. I used a similar method with the following (recorded) Macro. It uses Column A as the Control Column, B as the one that gets sorted and C as the Rand() between 0 and 1.

ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A60"), Type:=xlFillDefault
Range("A1:A60").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C60"), Type:=xlFillDefault
Range("C1:C60").Select
ActiveWindow.SmallScroll Down:=-66
Range("B1:C60").Select
Range("C1").Activate
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Select
Selection.ClearContents
Range("A1").Select
End Sub

I found it works ...great for Lottery Picks

Jonsi

### RE: how to randomize numbers in Excel?

JVF,
got EGG on my face --
I finally figured out what you and jonsi were doing  -- of course...

it works PERFECTLY!!

Hats off to a simple (except for this dummy) solution that is worthy of ANOTHER STAR.

### RE: how to randomize numbers in Excel?

Happens to me all the time.

I looked at your answer briefly and thought I must have missed something in the question.

Thanks for the star.  It's been hard to slip in a reply lately before someone else.

Hat's off to you !!

### RE: how to randomize numbers in Excel?

(OP)
thanks everyone!

i didn't quite get JVF's method at first either, but then i figured it out and got my column of random numbers!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!