Random Number Generator in Excel with No Repeats (9 Methods)

For specific purposes (e.g. phone number, lottery, statistical sampling), we have to generate random numbers without repetition. However, you may get repetitive random numbers if you use the general Excel formulas. In this article, I’ll discuss the 9 methods as a random number generator in Excel with no repeats along with the proper explanation.

Download Practice Workbook

9 Methods to Implement Random Number Generator in Excel with No Repeats

In the first 4 methods, you’ll see the use of newly released array functions, introduced in Excel 365, to generate random numbers without repetition. However, the rest methods are suitable for all Excel versions specially for those who are using the earlier versions of Excel. To sum up, you can use the 9 methods as a random number generator in Excel with no repeats.

Let’s go into the methods.

1. Using the RANDARRAY Function

Firstly, we’ll see the use of the RANDARRAY function to generate random numbers.

The RANDARRAY function, introduced in Excel 365, yields a list of random numbers in array form. And we may use the function to get random numbers without duplicate values.

For example, I want to produce 20 random numbers without repetition from 1 to 200.

In such conditions, the formula will be as follows-

Here, 10 is the number of rows, 2 is the number of columns, 1 is the minimum value, 200 is the maximum value, and lastly, TRUE is for integer numbers.

However, this method will be fruitful if you want a few numbers from a large range of numbers (e.g. generating 10/20 numbers from 1 to 200/500). Otherwise, it will create duplicate values.

Read More: Excel Formula to Generate Random Number (5 examples)

2. Using UNIQUE & RANDARRAY Functions

Secondly, we’ll use the application of the UNIQUE function as well as the RANDARRAY function.

The UNIQUE function, available in Excel 365, Excel 2021 versions, returns a list of unique values from a given dataset or cell range. So, we can utilize two functions to produce random numbers without repetition.

The combined formula will be-

Here, 10 is the number of rows, 2 is the number of columns, 1 is the minimum value, 200 is the maximum value, and lastly, TRUE is for integer numbers.

⧬ In the above formula, I used RANDARRAY(10,2,1,100,TRUE) as an array to generate 20 random numbers between 1 and 100. Later, the UNIQUE function will return unique values from the generated random numbers.

3. Applying SORTBY & SEQUENCE Functions to Generate Random Number with No Repeats

Thirdly, we can utilize the combination of some dynamic array functions.

The SEQUENCE function, accessible only to Excel 365 & Excel 2021 versions, produces a list (array) of sequential numbers.

Suppose, you want to get the list of numbers from 1 to 10, you need to use the following formula.

Here, 10 is the number of rows.

Next, the SORTBY function sorts an array of values based on another array of values with ascending or descending order. Hence, we may use the function along with the SEQUENCE & RANDARRAY function to create 10 random numbers without repetition.

⧬ While explaining the formula, we can say that RANDARRAY(10) produces a list of 10 random numbers. The SEQUENCE(10) syntax generates a list of 10 numbers (sequential). And SEQUENCE(10) and RANDARRAY(10) are used as array argument and by_array argument in the SORTBY function. Because we want to sort the sequential list of numbers by random order.

Read More: How to Generate Random Number with Excel VBA (4 Examples)

4. Utilizing the INDEX Function as Random Number Generator with No Repeats

If we want to produce a list of random numbers without repetition, then the INDEX function along with the previously discussed RANDARRAY, SEQUENCE & UNIQUE function will be highly effective. Also, we can produce 4 types of random numbers.

4.1. Producing Random Integer Numbers 

When you need to generate 10 random integer numbers between 1 and 100 without repetition, just use the following formula.

⧬ While explaining the formula, we can say that SEQUENCE(10) creates 10 sequential numbers, RANDARRAY(10, 1, 1, 100, TRUE) produces 10 random integer numbers between 1 and 100. Because TRUE is used for generating integer numbers. Later, the UNIQUE function removes the repetitive values from the generated numbers. Finally, the INDEX function returns the 10 random integer numbers as directed by the SEQUENCE function. In that case, the output UNIQUE function is used as an array.

4.2. Producing Random Decimal Numbers

If you want to generate 10 random decimal numbers without repetition, you may use the following formula.

Here, 10 is the number of rows, 2 is the number of columns, 1 is the minimum value, 100 is the maximum value, and lastly, FALSE is for generating decimal numbers.

4.3. Producing a Range of Integer Numbers

Similarly, you may generate a range of random integer numbers using the following formula.

Here, 20 is the number of rows, 1 is the number of columns, 1 is the minimum value, 200 is the maximum value, and lastly, TRUE is for generating integer numbers.

4.4. Producing a Range of Random Decimal Numbers

For generating a range of random decimal numbers between 1 and 100, use the following formula.

Here, 20 is the number of rows, 1 is the number of columns, 1 is the minimum value, 200 is the maximum value, and lastly, FALSE is for generating decimal numbers.

Read More: Generate Random Number in Excel with Decimals (3 Methods)

5. RAND & RANDBETWEEN Functions to Generate Random Number

The RAND function generates a number between 0 to 1. Fortunately, there is very little possibility of generating duplicate values while using the RAND function. You may get repetitive values if you cross the usage of 100000 times.

So, use the formula if you want to generate unique decimal numbers

Moreover, the RANDBETWEEN returns integer numbers between two given numbers.

For example, if you want to get the integer numbers between 1 and 100, you can use the formula below.

Here, 1 is the bottom argument and 100 is the top argument.

Unfortunately, there is much possibility of having repetitive values while using the RANDBETWEEN function. In such a case, you may use the Remove Duplicates option from the Data tab in the Data Tools ribbon after selecting the cell range.

Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)

Similar Readings

• Random 5 Digit Number Generator in Excel (7 Examples)
• Generate Random Number Between 0 and 1 in Excel (2 Methods)
• Random 4 Digit Number Generator in Excel (8 Examples)
• Generate Random Number from List in Excel (4 Ways)
• Random Number Generator between Range in Excel (8 Examples)

6. Applying RAND & RANK Functions as Random Number Generator

Furthermore, you can use the RANK function which returns the relative size of a number based on the given list of numbers. Before doing that create a list of random decimal numbers utilizing the RAND function.

Here, B5 is the starting cell of decimal numbers and B5:B15 is the cell range for decimal numbers.

Read More: Excel VBA: Random Number Generator with No Duplicates (4 Examples)

7. Utilizing the Combination of RANK.EQ & COUNTIF Functions

Let’s say you want to generate random numbers without repetition from 10 to 50.

In such a situation, you might use the combination of RANK.EQ & COUNTIF functions to generate repetition-free random numbers. Before doing that create a list of numbers between 10 and 50 using the RANDBETWEEN function.

Now, use the formula below-

Here, B5 is the starting cell of random numbers and B5:B15 is the cell range for decimal numbers.

⧬ While explaining the formula, we can say that the COUNTIF function is counting each random number that is available in the list. And the RANK.EQ returns the relative position (rank) for each random number, and finally, we need to add 9 because we want to generate the number starting from 10.

8. LARGE & MATCH Functions as Random Number Generator in Excel

Also, we can produce random integer numbers without repetition using the combination of the LARGE and MATCH functions. The LARGE function returns the kth largest value in a given cell range or dataset.

Here, $B$5:$B$15 is the cell range for random decimal numbers that are found using the RAND function, ROW(B1) refers to row number 1.

Next, we have to find the position of the created largest value using the following formula.

Here, C5 is the starting cell of the largest numbers, $B$5:$B$15 is the cell range of random decimal numbers, and finally, 0 is for getting an exact match.

9. Analysis Toolpak as Random Number Generator in Excel

Lastly, if you need to generate the random numbers without repetition instead of using the Excel formulas, you may use the following Add-ins of Excel.

For using the Add-ins, follow the steps below.

⇰ Go to File > Options.

⇰ Click on the Add-ins and select Excel Add-ins from the drop-down list and pick the option Go.

⇰ Then you’ll see the following dialog box, and check the box before the Analysis ToolPak and press OK.

⇰ Now, select the Data Analysis option from the Data tab in the Analysis ribbon.

⇰ Next, choose the option Random Number Generation and press OK.

⇰ Immediately, you’ll see the following dialog box.

⇰ Then choose the option based on your desired output.

⇰ For example, I selected Number of Variables and Number of Random Numbers as 4 & 10 respectively to generate the list of numbers having 10 rows and 4 columns.

⇰ More importantly, we must select the Distribution as Uniform because we want to avoid repetitive values.

⇰ Later, Between 10 and 30 means I want to find the number within the range.

⇰ Finally, you need to select the Output Range

After doing all the tasks, you’ll get the following output.

Read More: Random Number Generator with Data Analysis Tool and Functions in Excel

Some Common Errors

However, you may face the following errors while using the above formula as a random number generator in Excel with no repeats.

Conclusion

This is how you may use the above methods as a random number generator in Excel with no repeats. Now, choose any method based on your preference. I hope this article will ease your Excel Journey.

Related Articles

• How to Generate Random 10 Digit Number in Excel (6 Methods)
• Generate Random Number between Two Numbers in Excel (4 Ways)
• How to Generate Random Number in a Range with Excel VBA
• Random Number Generator with Normal Distribution in Excel (4 Methods)
• Generate Random Number with Mean and Standard Deviation in Excel
• Auto Generate Invoice Number in Excel (with 4 Quick Steps)