Random Selection from List with No Duplicates in Excel (5 Cases)

If you are looking for some of the easiest and most effective ways for random selection from a list with no duplicates in Excel, then you will find this article useful. So, let’s dive into the main article.

Here, we have the following dataset containing the sales records of some products. In this article, we will make a random selection of the products without duplicates using the following 5 methods.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

1. Using RAND, INDEX, and RANK.EQ Functions for Random Selection without Duplicates

Here, we will make a random selection of 6 products out of the total 9 products in the Random Item column and for making this selection free from duplicates we will generate some random numbers in the Random Value column. For making this selection we will use the RAND function, INDEX function, and RANK.EQ function (or RANK function if you prefer to use it).

Steps:
➤ For generating random unique numbers type the following function in cell C4.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will get the following random numbers and notice the effect of the volatile function RAND in changing the numbers after each calculation. You can see that before applying the AutoFill feature the value in the cell was 0.975686091 and after applying it the value changed to 0.082805271.

In this way, this function will automatically change those random values and will affect our selection also, to prevent this you can paste them as values.
➤ Select the range of the random values and press CTRL+C.
➤ After that, right-click on your mouse and select the Values option from different Paste Options.

Finally, you will get the fixed random values and now using them we will make our random selection.
➤ Type the following formula in cell F4.

Here, $B$4:$B$12 is the range of products, and $C$4:$C$12 is the range of random values.

➤ Press ENTER and drag down the Fill Handle tool.

Afterward, we made our random selection of 6 products among the 9 products avoiding any duplicate selection.

2. Using UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions

In this section, we are going to use the UNIQUE function, RANDARRAY function, INDEX function, and  RANK.EQ function to select any of the 6 unique products from the product list randomly.

Steps:
➤ To have the random unique numbers type the following function in cell C4.

Here, 9 is the total number of rows, 1 is the number of columns, 1 is the minimum number and 9 is the maximum number. Then RANDARRAY will give an array of this size of random numbers and UNIQUE will return the unique numbers from this array.

➤ After pressing ENTER and dragging down the Fill Handle tool you will have the following random numbers in the Random Value column.

As RANDARRAY is a volatile function, it will automatically change those random values and will affect our selection also, to prevent this we will paste them as values.
➤ Select the range of the random values and press CTRL+C.
➤ Then, right-click on your mouse and select the Values option from different Paste Options.

Afterward, you will get the fixed random values, and now using them we will make our random selection.
➤ Type the following formula in cell F4.

Here, $B$4:$B$12 is the range of products, and $C$4:$C$12 is the range of random values.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, we have done our random selection of the products without duplicates in the Random Item column.

The UNIQUE function and the RANDARRAY function are available only for Microsoft Excel 365 and Excel 2021 versions.

3. Random Selection with No Duplicates Using RAND, INDEX, RANK.EQ, and COUNTIF

Here, we will choose any 6 unique products from the list of the Product column randomly and then muster them in the Random Item column with the help of some random numbers. To do this we will be using the combination of the RAND function, INDEX function, RANK.EQ function, and COUNTIF function.

Steps:
➤ For generating random unique numbers apply the following function in the cells of the Random Value column.

As RAND is a volatile function, it will automatically change those random values and will affect our selection also, to prevent this we will paste them as values.
➤ Select the range of the random values and press CTRL+C.
➤ After that, right-click on your mouse and select the Values option from different Paste Options.

Then, you will have the stable random values, and now using them you can make our random selection.
➤ Apply the following formula in cell F4.

Here, $B$4:$B$12 is the range of products, and $C$4:$C$12 is the range of random values.

➤ Press ENTER and drag down the Fill Handle tool.

Eventually, we made our random selection of 6 products among the 9 products avoiding any duplicate selection.

4. Using Combination of INDEX, SORTBY, RANDARRAY, ROWS, and SEQUENCE Functions

In this section, we will make our random selection of the unique products without the need for the random numbers with the help of the INDEX function, SORTBY function, RANDARRAY function, ROWS function, and SEQUENCE function.

Steps:
➤ Use the following formula in cell E4.

Here, $B$4:$B$12 is the range of products.

After pressing ENTER, you will get the following 6 random products in the Random Item column.

The SORTBY function and the RANDARRAY function are available only for Microsoft Excel 365 and Excel 2021 versions.

5. Selection of a Whole Row from a List without Duplicates

You can choose for the whole row also which means you will get the corresponding sales value for any chosen product here. To do this task we will be using the combination of the INDEX function, SORTBY function, RANDARRAY function, ROWS function, and SEQUENCE function.

Steps:
➤ Write down the following formula in cell E4.

Here, B4:C12 is the range of products and sales values.

Immediately after pressing ENTER, you will get any of the random 6 products and their corresponding sales values.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Practice Workbook

Conclusion

In this article, we tried to show the ways to a random selection from a list with no duplicates in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

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