Eshrak Kader
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.
Download Practice Workbook
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 optional rows argument.
- 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 the array argument while the RANDARRAY(ROWS(B4:B12)) is the by_array_1 argument.
- Output → {“Amazon”;”Microsoft”;”Johnson & Johnson”;”Procter & Gamble”;”Oracle”;”TSMC”;”Chevron”;”Apple”;”Toyota”}
- SEQUENCE(3) → returns a sequence of numbers. Here, 3 is the rows argument.
- 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 the array argument while the SEQUENCE(3) is the row_num argument that indicates the row location.
- 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 the number argument while the $C$5:$C$13 range refers to the ref argument.
- 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 the range argument that refers to the first “Random Value”, while the C5 cell indicates the criteria argument that returns the count of the matched value.
- 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 the array argument which is the “Company” name. Next, RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1 is the row_num argument that indicates the row location. Lastly, 1 is the optional column_num argument that points to the column location.
- 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 the number argument while the $C$5:$C$13 range refers to the ref argument.
- Output → 7
- INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1) → in this expression, the $B$5:$B$13 is the array argument which is the “Company” name. Next, RANK.EQ(C5,$C$5:$C$13) is the row_num argument that indicates the row location. Lastly, 1 is the optional column_num argument that points to the column location.
- 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 optional rows argument.
- 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 the array argument and the RANDARRAY(ROWS(B5:C13)) is the by_array_1 argument.
- 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 the rows argument.
- 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 the array argument while the SEQUENCE(3) and {1,2} are the row_num and col_num arguments indicating the row and column locations respectively.
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 the logical_test argument that compares the values in the 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) (value_if_true argument) otherwise it returns Blank (value_if_false argument).
- Output → {FALSE;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE}
- COUNTIF(C5:C13,”>150″) → here, the C5:C13 cells represent the range argument that refers to the Stock Prices, while “>150” indicates the criteria argument that returns the count of the matched value.
- 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 the number argument.
- 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 the array argument while the INT(RAND()*COUNTIF(C5:C13,”>150″)+1) is the k-th argument.
- 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 the array argument which is the “Company” name. Next, LARGE(IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1),INT(RAND()*COUNTIF(C5:C13,”>150″)+1)) is the row_num argument that indicates the row location.
- 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 the rows argument, next the optional columns argument is left blank, then 1000 and 50 are the optional start and step arguments.
- Output → {1350;1400;1300;1200;1100;1000;1050;1250;1150}
- SORTBY(SEQUENCE(9, , 1000, 50), RANDARRAY(9)) → here, the SEQUENCE(9, , 1000, 50) is the array argument while the RANDARRAY(9) is the by_array_1 argument.
- 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.
