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.

**Table of Contents**hide

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

`=RAND()`

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

`=INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)`

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

becomes`RANK.EQ(C4,$C$4:$C$12)`

`RANK.EQ(0.617433431,$C$4:$C$12)`

→`RANK.EQ`

`returns the rank of the value`

`0.617433431`

`among other values in the range`

`$C$4:$C$12`

`.`

**Output →**`6`

becomes`INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)`

`INDEX($B$4:$B$12,6,1)`

→`INDEX`

`returns the value of cell`

`B9`

`at the intersection of`

`Row 6`

`and`

`Column 1`

`in the range`

`$B$4:$B$12`

`.`

**Output →**`Banana`

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

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

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

`=INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)`

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

becomes`RANK.EQ(C4,$C$4:$C$12)`

`RANK.EQ(1.761880408,$C$4:$C$12)`

→`RANK.EQ`

`returns the rank of the value`

`1.761880408`

`among other values in the range`

`$C$4:$C$12`

`.`

**Output →**`8`

becomes`INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)`

`INDEX($B$4:$B$12,8,1)`

→`INDEX`

`returns the value of cell`

`B11`

`at the intersection of`

`Row 8`

`and`

`Column 1`

`in the range`

`$B$4:$B$12`

`.`

**Output →**`Blackberry`

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

`=RAND()`

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

`=INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1,1)`

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

becomes`RANK.EQ(C4,$C$4:$C$12)`

`RANK.EQ(0.440349449,$C$4:$C$12)`

→`RANK.EQ`

`returns the rank of the value`

`0.440349449`

`among other values in the range`

`$C$4:$C$12`

`.`

**Output →**`6`

becomes`COUNTIF($C$4:C4,C4`

)`COUNTIF($C$4:C4,0.440349449)`

→`counts the number of cells having the value`

`440349449`

`in the range`

`$C$4:C4`

**Output →**`1`

becomes`RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1`

`6+1-1 → 6`

becomes`INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1,1)`

`INDEX($B$4:$B$12,6,1)`

→`INDEX`

`returns the value of cell`

`B9`

`at the intersection of`

`Row 6`

`and`

`Column 1`

`in the range`

`$B$4:$B$12`

`.`

**Output →**`Banana`

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

**Read More:**** Random Selection Based on Criteria in Excel**

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

`=INDEX(SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))), SEQUENCE(6))`

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

returns the total row numbers in this range`ROWS(B4:B12)`

→**Output →**9

becomes`RANDARRAY(ROWS(B4:B12))`

generates random 9 numbers`RANDARRAY(9)`

→**Output →**`{0.94536; 0.51383; 0.86142; 0.78644; 0.34980; 0.48125; 0.63824; 0.24971; 0.045946}`

becomes`SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12)))`

`SORTBY({“Orange”, “Apple”, “Watermelon”, “Walnut”, “Jackfruit”, “Banana”, “Cherry”, “Blackberry”, “Tomato”}, {0.94536; 0.51383; 0.86142; 0.78644; 0.34980; 0.48125; 0.63824; 0.24971; 0.045946})`

**Output →**`{“Watermelon”, “Blackberry”, “Walnut”, “Apple”, “Jackfruit”, “Banana”, “Cherry”, “Walnut”, “Tomato”, “Orange”}`

gives a range of serial numbers from 1 to 6`SEQUENCE(6)`

→**Output →**`{1; 2; 3; 4; 5; 6}`

becomes`INDEX(SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))), SEQUENCE(6))`

`INDEX(SORTBY({“Watermelon”, “Blackberry”, “Walnut”, “Apple”, “Jackfruit”, “Banana”, “Cherry”, “Walnut”, “Tomato”, “Orange”}, {1; 2; 3; 4; 5; 6})`

**Output →**`{“Watermelon”, “Blackberry”, “Walnut”, “Apple”, “Jackfruit”, “Banana”}`

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

**Read More:** Excel VBA: Random Selection from List

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

`=INDEX(SORTBY(B4:C12, RANDARRAY(ROWS(B4:C12))), SEQUENCE(6), {1,2})`

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

returns the total row numbers in this range`ROWS(B4:C12)`

→**Output →**9

becomes`RANDARRAY(ROWS(B4:B12))`

generates random 9 numbers`RANDARRAY(9)`

→**Output →**`{0.69680; 0.04111; 0.23072; 0.54573; 0.18970; 0.98737; 0.29843; 0.59124; 0.60439}`

becomes`SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12)))`

`SORTBY({“Orange”, 2721; “Apple”, 2172; “Watermelon”, 2958;“Walnut”, 3405; “Jackfruit”, 2154; “Banana”, 2580; “Cherry”, 4316; “Blackberry”, 4815; “Tomato”, 4792}, {0.94536; 0.51383; 0.86142; 0.78644; 0.34980; 0.48125; 0.63824; 0.24971; 0.045946})`

**Output →**`{“Tomato”, 4792; “Walnut”, 3405; “Blackberry”, 4815; “Banana”, 2580; “Apple”, 2172; “Cherry”, 4316; “Orange”, 2721; “Jackfruit”, 2154; “Watermelon”, 2958}`

gives a range of serial numbers from 1 to 6`SEQUENCE(6)`

→**Output →**`{1; 2; 3; 4; 5; 6}`

becomes`INDEX(SORTBY(B4:C12, RANDARRAY(ROWS(B4:C12))), SEQUENCE(6), {1,2})`

`INDEX(SORTBY({“Tomato”, 4792; “Walnut”, 3405; “Blackberry”, 4815; “Banana”, 2580; “Apple”, 2172; “Cherry”, 4316; “Orange”, 2721; “Jackfruit”, 2154; “Watermelon”, 2958}, {1; 2; 3; 4; 5; 6}, {1,2})`

**Output →**`{“Tomato”, 4792; “Walnut”, 3405; “Blackberry”, 4815; “Banana”, 2580; “Apple”, 2172; “Cherry”, 4316}`

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

**Read More:** How to Randomly Select Rows in Excel

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