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-

`=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.

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-

`=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.

### 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

**argument in the**

*by_array***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.

`=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.

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

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

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

**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

`=RAND()`

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.

`=RANDBETWEEN(1,100)`

Here, **1 **is the ** bottom **argument and

**100**is the

**argument.**

*top*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**

**Generate Random Number Between 0 and 1 in Excel (2 Methods)****Random 5 Digit Number Generator in Excel (7 Examples)****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.

`=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.

**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-

`=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.

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

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.

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

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

## 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)**

great thanksssssssssss Mr Md