# Random Numbers in a column using an Array

Location: http://www.mvps.org/dmcritchie/excel/random.htm
[View without Frames]

The usual request for Random numbers is how to generate random numbers without duplicates.  While these may no longer be random numbers it is a frequent request.  The following will work for small sets.  It might be better to do something else for larger sets because checking every previous numbers will take twice as long for each additional number.

Volatile functions such as CELL() OFFSET() TODAY() INDIRECT() NOW() INFO() RAND() may impact your system, see slowresp.htm#volatile.

## Generating a Random Number without duplicates

From: C Petrin (c.petrin..@..xtra.co.nz)
Subject: Re: Random Number
Newsgroups: microsoft.public.excel.programming

The routine below uses the worksheet function RANDBETWEEN to build an array.  (You will need a reference to Excel's Data Analysis Toolpak: tools, addins, check Analysis Toolpak).  Each random number generated is compared to existing values in the array to prevent duplication.

The following macro does not appear to work, in VBA regardless of attempts to set references to Toolpak and DAO within VBA and to use RANDBETWEEN or RANDOMBETWEEN.

```Dim MyRand(12), Invalid(12), iTest As Long
Dim GoodNum As Boolean

For x = 1 To 12
GoodNum = False
Do While GoodNum = False
iTest = RANDBETWEEN(1, 12) '-- not avaiable in VBA
For y = 1 To 12
If iTest = MyRand(y) Then
GoodNum = False
Exit For
Else
GoodNum = True
End If
Next y
Loop
MyRand(x) = iTest
Next x
```

To place the results into a column down from the active cell.
```For i = 1 to 12
activecell.offset(i-1,0) = MyRand(i)
next i
```

## Randomized Numbers 1 to 180 in a Column by Sorting Random Column

A simpler and probably faster solution, particularly as you increase the scope of the numbers, where each number must be represented..

Generate 1 to 180 down a column, and place =RAND() in the next empty column. Convert the random numbers to constants: copy (ctrl+c), edit, paste special, values and then sort on the random numbers column, finally eliminate the random numbers column.

Record a macro doing the above if you want to do this often.

I expect this would be more efficient than generating a random number between a range and then checking it to make sure that it doesn't duplicate a previous number.

```Option Explicit
Sub Macro11()
'create numbers 1 to 180 in column a, randomize the order
Range("a1").Formula = "1"
Range("a2").Formula = "2"
Range("A1:A2").AutoFill Range("A1:A180")
Range("b1").Formula = "=RAND()"
Range("b1").AutoFill Range("B1:B180")
Range("B1:B180").Select
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("1:180").Select
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("b:b").Delete
Range("a1").Select
End Sub
```
FWIW, Under 1 second on my machine, Upping from 180 to 64000 runs about 10 seconds.

Update: arrays are faster see replies by J.E.McGimpsey and David Braden in this thread   The array version by J.E.McGimpsey brute force rearranging ran in under 1 second for 64,000 rows.  probably runs faster by sorting arrays. Update: in the same thread Tushar Mehtar has variations and comments on timing, theoretical and resulting see his comments.

## Related

You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on July 14, 2001.