Excel allows us to create to generate random numbers list without duplicates using the RANDBETWEEN, RANK.EQ and COUNTIF functions. This step by step tutorial will assist all levels of Excel users to learn how to use the RANDBETWEEN function with no duplicates.
Figure 1. The final result of the formula
Syntax of the RANDBETWEEN Formula
=RANDBETWEEN(bottom, top)
The parameters of the RANDBETWEEN function are:
- bottom – a value from which we want to get a random value
- top – a value to which we want to get a random value
Syntax of the RANK.EQ Formula
The generic formula for the RANK.EQ function is:
=RANK.EQ(number, ref, [order])
The parameters of the RANK.EQ function are:
- number – a number for which we want to find the rank in the array
- ref – the array where we want to rank the number
- [order] – a type of ranking data in the ascending or the descending order (0 or omitted value).
Syntax of the COUNTIF Formula
The generic formula for the COUNTIF function is:
=COUNTIF(range, criteria)
The parameters of the COUNTIF function are:
- range – the range from which we want to count the non-blank cells
- criteria – the criteria for counting the cells in the range
Generate Random Number List With No Duplicates in Excel
In column B, we will first generate random numbers from 10 and 30 using the RANDBETWEEN function. As the function can generate duplicate numbers, in column C, we will generate a new list of numbers without duplicates.
The formula in column B looks like:
=RANDBETWEEN(10, 30)
The bottom parameter of the function is 10, while the top parameter is 30.
To apply the RANDBETWEEN formula, we need to follow these steps:
- Select cell B3 and click on it
- Insert the formula:
=RANDBETWEEN(10,30) - Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 2. Using the RANDBETWEEN formula to get random numbers 10-30 list
Now we can use the combination of RANK.EQ and COUNTIF functions to create a list without duplicates.
The formula in the cell C3 looks like:
=9 + RANK.EQ(B3, $B$3:$B$23) + COUNTIF($B$3:B3, B3) - 1
At the beginning of the formula, we must add 9 to the result, as the range begins from 10. The parameter range in the COUNTIF function is the cell range $B$3:B3 while the criteria is the cell B3. The number parameter of the RANK.EQ function is the cell B3, while the parameter ref is the range $B$3:$B$23. An order is omitted because we want to rank in descending order.
To apply the formula, we need to follow these steps:
- Select cell C3 and click on it
- Insert the formula:
=9+RANK.EQ(B3,$B$3:$B$23)+COUNTIF($B$3:B3,B3)-1 - Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 3. Generate a list of random numbers 10-30 with no duplicates
As we can see in Figure 3, column B has numbers 10-30 with duplicates, because only the RANDBETWEEN function is used. In column C, we have same numbers 10-30, but without duplicates using the RANK.EQ and COUNTIF functions.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment