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.

Download Workbook


5 Cases for Random Selection from List with No Duplicates in Excel

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.

Excel random selection from list no duplicates

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


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

Excel random selection from list no duplicates

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

=RAND()

RAND, INDEX, RANK.EQ Functions

➤ Press ENTER and drag down the Fill Handle tool.

RAND, INDEX, RANK.EQ Functions

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.

Excel random selection from list no duplicates

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.

RAND, INDEX, RANK.EQ Functions

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.

  • RANK.EQ(C4,$C$4:$C$12) becomes
    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
  • INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1) becomes
    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

RAND, INDEX, RANK.EQ Functions

➤ Press ENTER and drag down the Fill Handle tool.

RAND, INDEX, RANK.EQ Functions

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

Excel random selection from list no duplicates

Read More: How to Generate a Random String from a List in Excel (5 Suitable Ways)


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

Excel random selection from list no duplicates

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.

UNIQUE,RANDARRAY,INDEX,RANK.EQ Functions

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

Excel random selection from list no duplicates

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.

UNIQUE,RANDARRAY,INDEX,RANK.EQ Functions

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.

  • RANK.EQ(C4,$C$4:$C$12) becomes
    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
  • INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1) becomes
    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

UNIQUE,RANDARRAY,INDEX,RANK.EQ Functions

➤ Press ENTER and drag down the Fill Handle tool.

Excel random selection from list no duplicates

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

UNIQUE,RANDARRAY,INDEX,RANK.EQ Functions

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

Read More: How to Select Random Sample in Excel (4 Methods)


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

Excel random selection from list no duplicates

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

=RAND()

RAND,INDEX,RANK.EQ,COUNTIF Functions

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.

Excel random selection from list no duplicates

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.

  • RANK.EQ(C4,$C$4:$C$12) becomes
    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
  • COUNTIF($C$4:C4,C4) becomes
    COUNTIF($C$4:C4,0.440349449)counts the number of cells having the value 440349449 in the range $C$4:C4
    Output → 1
  • RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1 becomes
    6+1-1 → 6
  • INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1,1) becomes
    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

RAND,INDEX,RANK.EQ,COUNTIF Functions

➤ Press ENTER and drag down the Fill Handle tool.

RAND,INDEX,RANK.EQ,COUNTIF Functions

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

Excel random selection from list no duplicates

Read More: Random Selection Based on Criteria in Excel (3 Cases)


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

Excel random selection from list no duplicates

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.

  • ROWS(B4:B12)returns the total row numbers in this range
    Output → 9
  • RANDARRAY(ROWS(B4:B12)) becomes
    RANDARRAY(9) generates random 9 numbers
    Output → {0.94536; 0.51383; 0.86142; 0.78644; 0.34980; 0.48125; 0.63824; 0.24971; 0.045946}
  • SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))) becomes
    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”}
  • SEQUENCE(6)gives a range of serial numbers from 1 to 6
    Output → {1; 2; 3; 4; 5; 6}
  • INDEX(SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))), SEQUENCE(6)) becomes
    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”}

SORTBY,RANDARRAY,SEQUENCE functions

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

SORTBY,RANDARRAY,SEQUENCE functions

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 (3 Examples)


Method-5: Selection of a Whole Row from 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.

Excel random selection from list no duplicates

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.

  • ROWS(B4:C12)returns the total row numbers in this range
    Output → 9
  • RANDARRAY(ROWS(B4:B12)) becomes
    RANDARRAY(9) generates random 9 numbers
    Output → {0.69680; 0.04111; 0.23072; 0.54573; 0.18970; 0.98737; 0.29843; 0.59124; 0.60439}
  • SORTBY(B4:B12, RANDARRAY(ROWS(B4:B12))) becomes
    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}
  • SEQUENCE(6)gives a range of serial numbers from 1 to 6
    Output → {1; 2; 3; 4; 5; 6}
  • INDEX(SORTBY(B4:C12, RANDARRAY(ROWS(B4:C12))), SEQUENCE(6), {1,2}) becomes
    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}

Selection of a random row

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

Selection of a random row

Read More: How to Randomly Select Rows in Excel (2 Ways)


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.

Practice


Conclusion

In this article, we tried to show the ways for 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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo