Certainly, Microsoft Excel excels at arranging and manipulating data. Now, it is a well-known fact that Excel has numerous dedicated functions to randomize and generate numbers. Granted this, in this article, we’ll take a deep dive into **how to randomize a list in Excel without duplicates**. In addition, we’ll also learn to randomly select from a list, make selections based on criteria, and generate unique random numbers.

**Table of Contents** hide

Download Practice Workbook

4 Ways to Randomize a List Without Duplicates in Excel

1. Using INDEX, SORTBY, and SEQUENCE Functions (Applicable for Latest Excel Versions)

2. Utilizing INDEX, RANK.EQ, and COUNTIF Functions (Compatible with Older Version)

3. Employing RAND, INDEX, and RANK.EQ Functions

4. Applying UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions

How to Randomly Select from a List with No Duplicates in Excel

How to Randomly Select with Criteria in Excel

How to Make Unique Random Number Generator in Excel

Practice Section

Conclusion

## Download Practice Workbook

Randomizing a List in Excel without Duplicates.xlsx

## 4 Ways to Randomize a List Without Duplicates in Excel

To begin with, let’s assume the **List of Stock Prices **dataset shown in the **B4:C13 **cells containing the *“Company”* names and the *“Stock Prices” *in USD. Here, we want to return a random *“Company”* name from the list using various functions in Excel. Henceforth, let’s see each method on how to randomize a list in Excel without creating duplicates, with the appropriate illustrations.

Here, we have used the *Microsoft Excel 365* version; you may use any other version according to your convenience.

### 1. Using INDEX, SORTBY, and SEQUENCE Functions (Applicable for Latest Excel Versions)

First of all, let’s start by combining the INDEX, **SORTBY**, **RANDARRAY**, ROWS, and SEQUENCE functions, which collectively select 3 random *“Companies”* from the list shown in the **B4:B13** cells.

📌 ** Steps**:

- Initially, move to the
**B16**cell >> enter the expression given below.

`=INDEX(SORTBY(B5:B13, RANDARRAY(ROWS(B5:B13))), SEQUENCE(3))`

Here, the **B5:B13** array refers to the *“Company”* names.

**Formula Breakdown:**

**ROWS(B5:B13) →**returns the total row numbers in the given range.**Output → 9**

**RANDARRAY(ROWS(B4:B12)) →**returns an array of random numbers, in this case, 9 arrays. Here, the**ROWS(B4:B12)**is the optionalargument.*rows***Output → {0.278134626212438;0.148720604883087;0.355282358043423;0.036883208689009;0.832535669722357;0.927487306458828;0.223257349246205;0.241979490824856;0.100170115552212}**

**SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))) →**sorts a range or array based on the values in the corresponding range or arrays. Here, the**B4:B12**is theargument while the*array***RANDARRAY(ROWS(B4:B12))**is theargument.*by_array_1***Output → {“Amazon”;”Microsoft”;”Johnson & Johnson”;”Procter & Gamble”;”Oracle”;”TSMC”;”Chevron”;”Apple”;”Toyota”}**

**SEQUENCE(3) →**returns a sequence of numbers. Here,**3**is theargument.*rows***Output → {1;2;3}**

**=INDEX(SORTBY(B5:B13, RANDARRAY(ROWS(B5:B13))), SEQUENCE(3)) →**returns a value at the intersection of a row and column in a given range. In this expression, the**SORTBY(B5:B13, RANDARRAY(ROWS(B5:B13)))**is theargument while the*array***SEQUENCE(3)**is theargument that indicates the row location.*row_num***Output →****{“Chevron”, “Procter & Gamble”, “Toyota”}**

*📃** Note: This formula works for the Excel 365 and Excel 2021 versions, in case you’re using an older version of Excel, you can apply the next method.*

Boom! That’s how easy it is! This returns a randomized list in Excel without any duplicates.

**Read More:** How to Randomize List of Names in Excel (8 Easy Methods)

### 2. Utilizing INDEX, RANK.EQ, and COUNTIF Functions (Compatible with Older Version)

Alternatively, we can combine the RAND, **INDEX**, **RANK.EQ**, and COUNTIF functions to obtain a randomized list of the *“Company”* names. So just follow along.

- First, go to the
**C5**cell >> use the**RAND**function to generate a random value >> use the**Fill Handle tool**to copy the formula into the cells below.

- Second, select the
**C5:C13**cells >> press**CTRL + C**keys to copy the values.

- Third, choose the
**C5:C13**cells >> click the**Paste**drop-down >> select the**Paste Values**option.

- Finally, navigate to the
**B16**cell >> type in the following expression.

`=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1,1)`

In this case, the **B5:B13** and **C5:C13** ranges represent the *“Company”* and the *“Random Values”* respectively while the **C5** cell points to the first *“Random Value”*.

**Formula Breakdown:**

**RANK.EQ(C5,$C$5:$C$13)**→ returns the rank of a value in a list of numbers. Here, the**C5**cell is theargument while the*number***$C$5:$C$13**range refers to theargument.*ref***Output → 9**

**COUNTIF($C$5:C5,C5) →**counts the number of cells within a range that meet the given condition. Here, the**$C$5:C5**cells represent theargument that refers to the first*range**“Random Value”*, while the**C5**cell indicates theargument that returns the count of the matched value.*criteria***Output → 1**

**INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1,1) →**In this expression, the**$B$5:$B$13**is theargument which is the*array**“Company”*name. Next,**RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1**is theargument that indicates the row location. Lastly,*row_num***1**is the optionalargument that points to the column location.*column_num***Output → “Oracle”**

📃 *Note: **Please make sure to use *Absolute Cell Reference* by pressing the F4 key on your keyboard.*

**Read More: **How to Shuffle Numbers in Excel (7 Easy Methods)

### 3. Employing RAND, INDEX, and RANK.EQ Functions

Conversely, a simpler way involves using the **RAND**, **INDEX**, and **RANK.EQ** functions to retrieve 3 of the *“Company”* names at random.

📌 ** Steps**:

- To start with, follow the steps shown previously or the GIF to copy and paste the values in the
*“Random Value”*column.

- Afterward, enter the
**B16**cell >> insert the expression into the Formula Bar.

`=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1)`

For instance, the **B5:B13** and **C5:C13** range of cells indicate the *“Company”* and the *“Random Values”* and the **C5** cell represents the initial *“Random Value”*.

**Formula Breakdown:**

**RANK.EQ(C5,$C$5:$C$13)**→ here, the**C5**cell is theargument while the*number***$C$5:$C$13**range refers to theargument.*ref***Output → 7**

**INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1) →**in this expression, the**$B$5:$B$13**is theargument which is the*array**“Company”*name. Next,**RANK.EQ(C5,$C$5:$C$13)**is theargument that indicates the row location. Lastly,*row_num***1**is the optionalargument that points to the column location.*column_num***Output → “Chevron”**

**Read More:** How to Randomize a List in Excel Into Groups (5 Suitable Ways)

### 4. Applying UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions

Last but not least, we also apply the UNIQUE, **RANDARRAY**, **INDEX**, **RANK.EQ** functions to fetch a list of random values in Excel. So, let’s see it in action.

📌 ** Steps**:

- First of all, click the
**C5**cell >> insert the following formula.

`=UNIQUE(RANDARRAY(9,1,1,9))`

In the above formula, **9** is the row number, **1** is the column number, **1** is the minimum number, and **9** is the maximum number. Next, the **UNIQUE **function ensures that the **RANDARRAY** function returns an array of unique numbers.

📃 *Note: **To stop the C5:C13 array from changing, copy and paste only the values or follow the steps shown in the prior method.*

- Following this, apply the following equation to the
**B16**cell.

`=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1)`

In this scenario, the **B5:B13** and **C5:C13** range point to the *“Company”* and the *“Random Values”*.

Subsequently, this concludes the process of how to a randomize list in Excel without duplicates.

**Read More:** How to Shuffle an Array Using Excel VBA (4 Examples)

## How to Randomly Select from a List with No Duplicates in Excel

For one thing, we can choose entire rows at random from a list, on this occasion, we can obtain the *“Company”* names and their corresponding *“Stock Prices”* using the **SORTBY**, **RANDARRAY**, **INDEX**, **SEQUENCE, **and **ROWS** functions.

📌 ** Steps**:

- At the very beginning, click to enter the
**B16**cell >> copy and paste the equation given below.

`=INDEX(SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13))), SEQUENCE(3), {1,2})`

**Formula Breakdown:**

**ROWS(B5:C13) → 9****RANDARRAY(ROWS(B5:C13)) →**here, the**ROWS(B5:C13)**is the optionalargument.*rows***Output → {0.0140698270891861;0.336601258084547;0.302828885068347;0.458536948594194;0.349731499694981;0.188127312170481;0.901260642146929;0.455208105393427;0.480186486777415}**

**SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13)))****→**here, the**B5:C13**is theargument and the*array***RANDARRAY(ROWS(B5:C13))**is theargument.*by_array_1***Output → {“TSMC”,81.75;”Toyota”,154.17;”Amazon”,106.21;”Apple”,136.72;”Microsoft”,256.83;”Procter & Gamble”,143.79;”Johnson & Johnson”,177.51;”Chevron”,144.78;”Oracle”,69.87}**

**SEQUENCE(3) →**here,**3**is theargument.*rows***Output → {1;2;3}**

**INDEX(****SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13))), SEQUENCE(3), {1,2}****) →**in this expression, the**SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13)))**is theargument while the*array***SEQUENCE(3)**and**{1,2}**are theand*row_num*arguments indicating the row and column locations respectively.*col_num*

## How to Randomly Select with Criteria in Excel

Moreover, Excel enables us to randomly select values based on set criteria. For instance, we’ll use the **INDEX**, LARGE, IF, ROW, INT, **RAND**, and **COUNTIF **functions to calculate the *“Company”* with a *“Stock Price”* worth more than *“$150 USD”*.

📌 ** Steps**:

- In the first place, enter the
**C16**cell >> insert the expression given below.

`=INDEX(B5:B13,LARGE(IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1),INT(RAND()*COUNTIF(C5:C13,">150")+1)))`

**Formula Breakdown:**

**IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**C5:C13>150**is theargument that compares the values in the*logical_test***C5:C13**range with**150**. If this value is greater than or equal to**150**then the function returns**ROW(C5:C13)-ROW(C5)+1)**(argument) otherwise it returns*value_if_true***Blank**(argument).*value_if_false***Output → {FALSE;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE}**

**COUNTIF(C5:C13,”>150″) →**here, the**C5:C13**cells represent theargument that refers to the*range**Stock Prices*, while**“>150”**indicates theargument that returns the count of the matched value.*criteria***Output → 3**

**INT(RAND()*COUNTIF(C5:C13,”>150″)+1) →**rounds a number down to the nearest integer. Here,**RAND()*COUNTIF(C5:C13,”>150″)+1**is theargument.*number***0.305982491187225 * 3 + 1 → 3**

**LARGE(IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1),INT(RAND()*COUNTIF(C5:C13,”>150″)+1))→**returns the k-th largest in a dataset. Here,**IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1)**is theargument while the*array***INT(RAND()*COUNTIF(C5:C13,”>150″)+1)**is the*k-th***Output → 8**

**INDEX(B5:B13,LARGE(IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1),INT(RAND()*COUNTIF(C5:C13,”>150″)+1))) →**In this expression, the**B5:B13**is theargument which is the*array**“Company”*name. Next,**LARGE(IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1),INT(RAND()*COUNTIF(C5:C13,”>150″)+1))**is theargument that indicates the row location.*row_num***Output → “Toyota”**

*📃** Note: In the *

*Microsoft Excel 365 version**, you can run the array formula by pressing the*

**ENTER**key. But, in the older versions of Excel, you must press**CTRL + SHIFT + ENTER**to use the array formula.Admittedly, some of the Methods to randomly select with criteria have been skipped, which you may explore if you wish.

## How to Make Unique Random Number Generator in Excel

Finally, we’ll discuss how to make a unique random number generator in Excel by utilizing the **SORTBY**, **SEQUENCE**, and **RANDARRAY** functions.

📌 ** Steps**:

- First and foremost, insert the formula into the
**B5**cell as shown in the image below.

`=SORTBY(SEQUENCE(9, , 1000, 50), RANDARRAY(9))`

**Formula Breakdown:**

**SEQUENCE(****9, , 1000, 50****) →**here,**9**is theargument, next the optional*rows*argument is left*columns***blank**, then**1000**and**50**are the optionaland*start*arguments.*step***Output → {1350;1400;1300;1200;1100;1000;1050;1250;1150}**

**SORTBY(****SEQUENCE(9, , 1000, 50), RANDARRAY(9)****) →**here, the**SEQUENCE(9, , 1000, 50)**is theargument while the*array***RANDARRAY(9)**is theargument.*by_array_1***Output → {1350;1400;1300;1200;1100;1000;1050;1250;1150}**

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

In short, this tutorial explores all the ins and outs of how to randomize a list in Excel without duplicates. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section. Or, you can check out our other articles related to Excel functions on ExcelDemy.

## Related Articles

- How to Do Factorials in Excel (6 Easy Methods)
- Shuffle Data in Excel (7 Simple Methods)