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.
=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.
RANK.EQ(C4,$C$4:$C$12)becomesRANK.EQ(0.617433431,$C$4:$C$12)→RANK.EQreturns the rank of the value0.617433431among other values in the range$C$4:$C$12.
Output →6
INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)becomesINDEX($B$4:$B$12,6,1)→INDEXreturns the value of cellB9at the intersection ofRow 6andColumn 1in 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.
RANK.EQ(C4,$C$4:$C$12)becomesRANK.EQ(1.761880408,$C$4:$C$12)→RANK.EQreturns the rank of the value1.761880408among other values in the range$C$4:$C$12.
Output →8
INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12),1)becomesINDEX($B$4:$B$12,8,1)→INDEXreturns the value of cellB11at the intersection ofRow 8andColumn 1in 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.
RANK.EQ(C4,$C$4:$C$12)becomesRANK.EQ(0.440349449,$C$4:$C$12)→RANK.EQreturns the rank of the value0.440349449among other values in the range$C$4:$C$12.
Output →6COUNTIF($C$4:C4,C4) becomesCOUNTIF($C$4:C4,0.440349449)→counts the number of cells having the value440349449in the range$C$4:C4
Output →1RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1becomes6+1-1 → 6INDEX($B$4:$B$12,RANK.EQ(C4,$C$4:$C$12)+COUNTIF($C$4:C4,C4)-1,1)becomesINDEX($B$4:$B$12,6,1)→INDEXreturns the value of cellB9at the intersection ofRow 6andColumn 1in 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.
ROWS(B4:B12)→ returns the total row numbers in this range
Output → 9
RANDARRAY(ROWS(B4:B12))becomesRANDARRAY(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)))becomesSORTBY({“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))becomesINDEX(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.
ROWS(B4:C12)→ returns the total row numbers in this range
Output → 9
RANDARRAY(ROWS(B4:B12))becomesRANDARRAY(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)))becomesSORTBY({“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})becomesINDEX(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.
