## how to randomize numbers in Excel?

## 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?

Can anything in Excel help me? Or any of the other Office programs?

## RE: how to randomize numbers in Excel?

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

## RE: how to randomize numbers in Excel?

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

Skip,

metzgsk@voughtaircraft.com

## RE: how to randomize numbers in Excel?

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.

Skip,

metzgsk@voughtaircraft.com

## RE: how to randomize numbers in Excel?

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

Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _

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?

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.

Skip,

metzgsk@voughtaircraft.com

## RE: how to randomize numbers in Excel?

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?

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