Formulation of the problem
Let’s assume that we need to create a set of integer random numbers without repetitions in a given range of values. Examples on the go:
- generating unique random codes for products or users
- assigning people to tasks (each one randomly from the list)
- permutation of words in the search query (hello seo-shnikam)
- playing lotto etc.
Method 1. Simple
To begin with, let’s consider a simple option: we need to get a random set of 10 integers from 1 to 10. Using the function built into Excel BETWEEN THE CASE (EDGE BETWEEN) uniqueness is not guaranteed. If you enter it in a sheet cell and copy it down 10 cells, then repetitions can easily happen:
Therefore, we will go the other way.
All versions of Excel have a function RANK (RANG), intended for ranking or, in other words, determining the top position of a number in a set. The largest number in the list has rank=1, the second in the top has rank=2, and so on.
Let’s enter the function in cell A2 SLCHIS (RAND) without arguments and copy the formula down 10 cells. This function will generate us a set of 10 random fractional numbers from 0 to 1:
In the next column we introduce the function RANKto determine the position in the ranking for each received random number:
We get in column B what we wanted – any desired number of non-repeating random integers from 1 to 10.
Purely theoretically, a situation may arise when SLCHIS will give us two identical random numbers in column A, their ranks will match and we will get a repetition in column B. However, the probability of such a scenario is extremely small, given the fact that the accuracy is 15 decimal places.
Method 2. Complicated
This method is slightly more complicated, but uses only one array formula. Let’s say we need to create a list of 9 non-repeating random integers in the range from 1 to 50 on a sheet.
Enter the following formula in cell A2, click at the end Ctrl + Shift + Enter (to enter it as an array formula!) and copy the formula down to the desired number of cells:
Method 3. Macro
And, of course, you can solve the problem using programming in Visual Basic. In one of the old articles about random sampling, I already cited the Lotto array macro function, which produces the required number of random non-repeating numbers from a given interval.
- How to count the number of unique values in a range
- Random selection of elements from a list