Random Numbers in a column using an Array

Location: http://www.mvps.org/dmcritchie/excel/random.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.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
Date: 2000/10/08 http://groups.google.com/groups?as_umsgid=39DF4019.5C980D5A%40xtra.co.nz

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
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        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.

Q86523 -- Excel: Random Number Generation, how Excel generates pseudo-random numbers:

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.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Random Numbers in a column using an Array

Generating a Random Number without duplicates

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

Related