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

Get FREE Advanced Excel Exercises with Solutions!

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.


Random Number Generator in Excel with No Repeats: 9 Methods

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, especially 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. 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-

=RANDARRAY(10,2,1,200,TRUE)

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.

Using the RANDARRAY Function1

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.


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, and 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-

=UNIQUE(RANDARRAY(10,2,1,100,TRUE))

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.

Using UNIQUE & RANDARRAY Functions


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.

=SEQUENCE(10)

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.

=SORTBY(SEQUENCE(10),RANDARRAY(10))

⧬ 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.

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


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.

=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100, TRUE)), SEQUENCE(10))

⧬ 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.

Utilizing the INDEX Function As Random Number Generator with No Repeats


4.2. Producing Random Decimal Numbers

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

=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100, FALSE)), SEQUENCE(10))

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.

As Random Number Generator Excel with No Repeats Utilizing the INDEX Function


4.3. Producing a Range of Integer Numbers

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

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 200, TRUE)), SEQUENCE(10,2))

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.

Utilizing the INDEX Function As Random Number Generator with No Repeats


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.

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 100)), SEQUENCE(10, 2))

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.

Utilizing the INDEX Function As Random Number Generator with No Repeats


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

=RAND()

RAND & RANDBETWEEN Functions

Moreover, the RANDBETWEEN function 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.

=RANDBETWEEN(1,100)

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

RAND & RANDBETWEEN Functions

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.

RAND & RANDBETWEEN Functions


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.

=RANK(B5,$B$5:$B$15)

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

Applying RAND & RANK Functions


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-

=9+RANK.EQ(B5, $B$5:$B$15) + COUNTIF($B$5:B5, B5) - 1

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.

Utilizing the combination of RANK.EQ & COUNTIF Functions


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.

=LARGE($B$5:$B$15,ROW(B1))

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.

LARGE & MATCH Functions

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

=MATCH(C5,$B$5:$B$15,0)

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.

LARGE & MATCH Functions


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.

To use 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.

Analysis Toolpak as Random Number Generator in Excel

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

Analysis Toolpak as Random Number Generator in Excel

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

Analysis Toolpak as Random Number Generator in Excel

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

Analysis Toolpak as Random Number Generator in Excel

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

⇰ Then choose the option based on your desired output.

Analysis Toolpak as Random Number Generator in Excel

⇰ 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.

Analysis Toolpak as Random Number Generator 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.

Name of Errors When Occurs
#CALC! If the UNIQUE function cannot extract the unique values.
#SPILL! If there is any value in the spill range where the UNIQUE function will return the list.
#VALUE! The RANDARRAY function occurs when the minimum value is larger than the maximum value.

Download Practice Workbook


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.


<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

1 Comment
  1. great thanksssssssssss Mr Md

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo