Contents
An infrequent but interesting task: randomly select N elements from a data array (list). There may be several reasons for its occurrence, for example:
- The amount of data is too large, so we are satisfied with analyzing a random sample from the full data set.
- Selection of winners from among the participants of any contest or lottery.
In any case, we are faced with the task of selecting a randomly given number of elements from a set (for example, this one):
Method 1. Random sort
Add another column to our list and insert a random number generation function into it SLCHIS (RAND). Then sort our list by the added column (Data – Sorting) and take the first N elements from the resulting table:
The disadvantages of this method are obvious – we will have to manually re-sort the list each time if we need to make another random selection. The pros are simplicity and accessibility.
Method 2: SMALL function
This way is to use the function LEAST (SMALL) to select from a list of N positions with the smallest random number in column A:
After choosing the five (in our example) smallest random numbers from column A, we pull out the names that match these numbers using the VLOOKUP function.
Method 3. Random Sampling Without Repeats – Lotto Function in VBA
You can create a simple VBA function that will produce a given number of random numbers from the desired interval. Open the Visual Basic Editor (ALT + F11 or in older versions of Excel via the menu Service – Macro – Visual Basic Editor), insert a new module through the menu Insert – Module and copy the text of the following function there:
Function Lotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Dim Out(1000) As Variant Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i j = 0 For i = Bottom To Bottom + Amount - 1 Out(j) = iArr(i) j = j + 1 Next i Lotto = Application.Transpose(Out) End Function
This function will have three arguments:
- Bottom — lower bound of the interval of random numbers
- Top is the upper bound of the interval of random numbers
- Amount – the number of random numbers that we want to select from the interval
That is, for example, to select 5 random numbers from 10 to 100, you will need to enter =Lotto(10;100;5)
Now this function is easy to use for selecting random values. Let’s add a numbered column to our list and select people by random numbers generated by the Lotto function:
Note that our Lotto function must be entered as an array formula, i.e. first you need to select the range of result cells (D2:D6) then enter our function Lot and, after entering the function arguments, press Ctrl + Shift + Enterto enter this function exactly as an array function in all selected cells.
Well, then it will remain with the help of the already familiar function VPR (VLOOKUP) pull names from the list corresponding to random numbers.
- Using the VLOOKUP function to substitute values
- Creating macros and user-defined functions in VBA
- RandomSelect function from PLEX add-on