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 2nd 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
Arr = rList.Value
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
PickRandomFromList = lArr
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.
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.