Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()
MultiCat()
PageSetupXL4M()
PrevSheet()
RandInt()
SampleNoReplace()
SumBold()
UnixToXLTime()

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Random Integers without repetition

XL's Analysis Toolpak Add-in has a function that returns random integers between two values: RANDBETWEEN(start, end).

Unfortunately, the function returns only a single value, so entering a range of cells with =RANDBETWEEN(1, 100) won't guarantee that there won't be duplicates.

A worksheet method for returning non-repeating integers

The easiest way to return non-repeating integers is to place the numbers you want to return in a column. Then place the formula =RAND() in an adjacent column and copy down for as many rows as you have numbers. Choose Data/Sort, and sort on the column with the random integers. You can then take as many numbers as you wish. The act of sorting will cause the random numbers to recalculate, so you can repeat the sort to get a new set.

Example:

Suppose you wanted 10 non-repeating numbers between 1 and 100 (inclusive). Put 1..100 in A1:A100. Enter =RAND() in B1 and copy down to B100. Choose Data/Sort and sort on Column B. You can then use A1:A10 for your data set. Sort again on Column B for a new set of 10.

A User Defined Function to return non-repeating integers

The following UDF will return an array of values that doesn't repeat.

Usage:

=RandInt(nStart, nEnd)

Returns a long integer between nStart and nEnd. If the function is array-entered, will return an array of N values where N is the number of cells the function is entered into.

Arguments:

nStart: (Optional) the minimum value to be returned, between 
        -2147483647 and 2147483647. The default value is 1.
nEnd:   (Optional), the maximum value to be returned, between 
        -2147483647 and 2147483647. If no value is provided, or 
        if nEnd < nStart, nEnd defaults to nStart + the number 
        of cells in the array - 1.

Example:

To get the same set of 10 numbers between 1 and 100 as in the last example, select A1:A10 and array enter

=RandInt(1, 100)

Note that array-entering =RandInt() in the range A1:B3 will return a three-row, two-column array of {1,2,3,4,5,6} in random order. If the range of integers between nStart and nEnd is smaller than the number of array-entered cells, the function returns the #NUM! error.

Note also that this function is volatile, so the numbers will change every time a cell on the worksheet is calculated.

   '******************************************************
   'Purpose: produce array of random ints in a range
   'Inputs:  nStart - the smallest value in the range
   '         nEnd - the largest value in the range
   'Returns: array of integers
   '
   'Revised: 14 June 2005 to avoid randomizing vArr()
   '******************************************************
    Public Function RandInt( _
        Optional ByVal nStart As Long = 1&, _
        Optional ByVal nEnd As Long = -2147483647) As Variant
        'returns array of random longs
        'J.E. McGimpsey  http://www.mcgimpsey.com/excel/randint.html
        Dim vArr As Variant
        Dim vResult As Variant
        Dim nCount As Long
        Dim nIndex As Long
        Dim nRand As Long
        Dim nRows As Long
        Dim nCols As Long
        Dim i As Long
        Dim j As Long
        Application.Volatile
        If Not TypeOf Application.Caller Is Range Then Exit Function
        With Application.Caller
            nCount = .Count
            If nEnd < nStart Then nEnd = nStart + nCount - 1
            If nCount = 1 Then
                RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
            ElseIf nCount > nEnd - nStart + 1 Then
                RandInt = CVErr(xlErrNum)
            Else
                nRows = .Rows.Count
                nCols = .Columns.Count
                nIndex = nEnd - nStart + 1
                ReDim vResult(1 To nRows, 1 To nCols)
                ReDim vArr(0 To nIndex - 1)
                For i = 0 To UBound(vArr)
                    vArr(i) = i + nStart
                Next i
                For i = 1 To nRows
                    For j = 1 To nCols
                        nRand = Int(Rnd() * nIndex)
                        nIndex = nIndex - 1
                        vResult(i, j) = vArr(nRand)
                        vArr(nRand) = vArr(nIndex)
                     Next j
                Next i
                RandInt = vResult
            End If
        End With
    End Function

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

Where to put UDFs and Macros

Not familiar with UDFs? See David McRitchie's Getting Started with Macros and User Defined Functions