McGimpsey & Associates : Excel : Sampling without duplication

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

Sampling a range without replacement

If you need to draw a random sample from a range, you might start with using the RAND() and INDEX() functions. Say you wanted a random sample from A1:A100:

=INDEX($A$1:$A$100,RAND()*100+1)

This works fine for one sample, or if you want more than one value and don't care if you get duplicates (just copy the formula to other cells). If instead you want to avoid duplication, you need to sample "without replacement" (imagine a hat with 100 slips of paper with the numbers 1 to 100 - if you take slips out without replacing them, you're guaranteed not to get duplication).

There are two "standard" worksheet methods for accomplishing this. In addition, I provide a User Defined Function (UDF) that is somewhat more flexible in that the source and destination ranges need not be columns or rows.

Note: If you just want a series of random integers, see Random integers without repetition.

Worksheet Method 1: Sorting on a helper column

The easiest way to return non-repeating values is to place them 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 your list were in A1:A100 and you wanted 10 random but non-duplicating samples. 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.

Worksheet Method 2: Using SMALL() with a helper column

If you don't want to sort your list, enter the =RAND() function in the helper column as in method 1, then in a third column select the cells to contain the sample values. Say you want 4 values, so select C1:C4. Then array-enter (holding down CTRL-SHIFT-ENTER for Windows or CMD-RETURN for Macs):

=INDEX(A1:A100,MATCH(SMALL(B1:B100,{1;2;3;4}),B1:B100,0))

A User Defined Function to sample without replacement

The following UDF will return an array of values that doesn't repeat (unless there are repetitions in the source range)

Usage:

=SampleNR(rng)

Returns a value from the range rng. If the function is array-entered, it will return an array of N values where N is the number of cells the function is entered into.

Arguments:

rng:    The source range to be sampled

Example:

To get the same sample of 4 values from the range A1:A100 as in the last example, select B1:B4 and array enter

=SampleNR(A1:A100)

Note that the sample range needn't be the same shape as the source range. Array-entering =SampleNR(A1:A100) in B1:D3 will return a 3 row by 3 column array of values.

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

   '*******************************************************
   'Purpose: produce an random sample without replacement
   'Entry:   Array-enter into range no larger than rSource
   'Inputs:  rSource: the range to be sampled
   'Returns: array of values from the source range. Returns 
   '         #N/A if  sample range > than source range
   '*******************************************************
   Public Function SampleNR(rSource As Range) As Variant
      Dim vTemp As Variant
      Dim nArr() As Long
      Dim nSource As Long
      Dim nDest As Long
      Dim nRnd As Long
      Dim nTemp As Long
      Dim i As Long
      Dim j As Long
        
      Application.Volatile
      nSource = rSource.Count
      With Application.Caller
         ReDim vTemp(1 To .Rows.Count, 1 To .Columns.Count)
         nDest = .Count
      End With
      If nDest > nSource Then
         SampleNR = CVErr(xlErrNA)
      Else
         ReDim nArr(1 To nSource)
         For i = 1 To nSource
            nArr(i) = i
         Next i
         For i = 1 To nDest
            nRnd = Int(Rnd() * (nSource - i + 1)) + i
            nTemp = nArr(nRnd)
            nArr(nRnd) = nArr(i)
            nArr(i) = nTemp
         Next i
         nTemp = 1
         For i = 1 To UBound(vTemp, 1)
            For j = 1 To UBound(vTemp, 2)
               vTemp(i, j) = rSource(nArr(nTemp))
               nTemp = nTemp + 1
            Next j
         Next i
         SampleNR = vTemp
      End If
   End Function

This page last updated Wednesday, 16 June 2004

McGimpsey & Associates

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

Sampling a range without replacement

Worksheet Method 1: Sorting on a helper column

Worksheet Method 2: Using SMALL() with a helper column

A User Defined Function to sample without replacement

Quick Links