#Excel: Generating a Random Sampling From a List Using VBA and the TEXTJOIN Function by David Hager

You might have a need to generate a random sampling of items from an Excel list. The technique presented here accomplishes this without any helper columns. The PickRandomFromList VBA function shown below returns a random array of items from a worksheet list, the size determined by the 2^nd argument of the function.

Function PickRandomFromList(rList As Range, sArray As Integer) As Variant

Dim N As Long

Dim Arr() As Variant

Dim lArr() As Variant

Dim Temp As Variant

Dim J As Long

Application.Volatile False

Arr = rList.Value

Randomize

ReDim lArr(LBound(Arr) To sArray

For N = 1 To sArray

J = CLng(((UBound(Arr) – N) * Rnd) + N)

Temp = Arr(N, 1)

lArr(N) = Arr(J, 1)

Arr(J, 1) = Temp

Next N

PickRandomFromList = lArr

End Function

It is important to note that this UDF does not recalculate with every change in the worksheet by using the line of code Application.Volatile False. It will only recalculate if a change is made in the cell containing the formula or in the specified worksheet range. The conversion of the array to a delimited list is done through the use of the TEXTJOIN function.

=TEXTJOIN(“,”,,PickRandomFromList(A2:A22,5))

This technique may be particularly useful for the selection of random committees from an employee list. I hope that this will give you some ideas about situations requiring random sampling.

You can download the workbook here.

PickList