How to Randomize a List Without Duplicates in Excel

The dataset showcases a List of Stock Prices.

Method 1 – Using the INDEX, SORTBY, and SEQUENCE Functions

Steps:

Select B16 and enter the formula below.

=INDEX(SORTBY(B5:B13, RANDARRAY(ROWS(B5:B13))), SEQUENCE(3))

B5:B13 refers to the “Company” names.

Formula Breakdown:

ROWS(B5:B13) → returns the total row number in the given range.
- Output → 9
RANDARRAY(ROWS(B4:B12)) → returns an array of random numbers, here, 9. 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, B4:B12 is the array argument and 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, SORTBY(B5:B13, RANDARRAY(ROWS(B5:B13))) is the array argument and 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, if you’re using an older version of Excel, apply the next method.

The result is a randomized list without duplicates.

Method 2 – Utilizing the INDEX, RANK.EQ, and COUNTIF Functions

Steps:

Go to C5 >> use the RAND function to generate a random value >>Drag down the Fill Handle to see the result in the rest of the cells.

Select C5:C13 >> press CTRL + C to copy the values.

Choose C5:C13 >> click Paste >> select Paste Values.

Go to B16 and enter the following formula.

=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1,1)

B5:B13 and C5:C13 represent “Company” and “Random Values” . 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. C5 is the number argument and $C$5:$C$13 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. $C$5:C5 represents the range argument that refers to the first “Random Value”. C5 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) → $B$5:$B$13 is the array argument: the “Company” name. RANK.EQ(C5,$C$5:$C$13)+COUNTIF($C$5:C5,C5)-1 is the row_num argument that indicates the row location. 1 is the optional column_num argument that points to the column location.
- Output → “Oracle”

Note: Use Absolute Cell Reference by pressing F4.

Method 3 – Using the RAND, INDEX, and RANK.EQ Functions

Steps:

Follow the previously described steps or observe the GIF to copy and paste values in the “Random Value” column.

Select B16 >> enter the formula in the Formula Bar.

=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1)

B5:B13 and C5:C13 indicate “Company” and “Random Values” and C5 represents the initial “Random Value”.

Formula Breakdown:

RANK.EQ(C5,$C$5:$C$13) → C5 is the number argument and $C$5:$C$13 refers to the ref argument.
- Output → 7

INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1) → $B$5:$B$13 is the array argument: the “Company” name. RANK.EQ(C5,$C$5:$C$13) is the row_num argument that indicates the row location. 1 is the optional column_num argument that points to the column location.
- Output → “Chevron”

Method 4 – Applying the UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions

Steps:

Select C5 and enter the formula below.

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

9 is the row number, 1 is the column number, 1 is the minimum number, and 9 is the maximum number. The UNIQUE function ensures the RANDARRAY function returns an array of unique numbers.

Note: To stop C5:C13 from changing, copy and paste the values only or follow the steps shown in the previous method.

Use the following equation in B16.

=INDEX($B$5:$B$13,RANK.EQ(C5,$C$5:$C$13),1)

B5:B13 and C5:C13 point to “Company” and “Random Values”.

How to Randomly Select from a List with No Duplicates in Excel

Get the “Company” names and their corresponding “Stock Prices:

Steps:

Enter the formula in B16.

=INDEX(SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13))), SEQUENCE(3), {1,2})

Formula Breakdown:

ROWS(B5:C13) → 9
RANDARRAY(ROWS(B5:C13)) → 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))) → B5:C13 is the array argument and 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) → 3 is the rows argument.
- Output → {1;2;3}

INDEX(SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13))), SEQUENCE(3), {1,2}) → SORTBY(B5:C13, RANDARRAY(ROWS(B5:C13))) is the array argument and SEQUENCE(3) and {1,2} are the row_num and col_num arguments indicating the row and column locations.

How to Randomly Select with Criteria in Excel

To find the “Company” with a “Stock Price” greater than “$150 USD”.

Steps:

Enter the formula in C16.

=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 if FALSE. C5:C13>150 is the logical_test argument that compares the values in C5:C13 range with 150. If this value is greater than or equal to 150, 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″) → C5:C13 represents the range argument that refers to the Stock Prices, and “>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. 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. IF(C5:C13>150,ROW(C5:C13)-ROW(C5)+1) is the array argument and 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))) → B5:B13 is the array argument: the “Company” name. 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 older versions of Excel, press CTRL + SHIFT + ENTER to use an array formula.

How to Create a Unique Random Number Generator in Excel

Steps:

Enter the formula in B5.

=SORTBY(SEQUENCE(9, , 1000, 50), RANDARRAY(9))

Formula Breakdown:

SEQUENCE(9, , 1000, 50) → 9 is the rows argument, the optional columns argument is left blank, 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)) → SEQUENCE(9, , 1000, 50) is the array argument and RANDARRAY(9) is the by_array_1 argument.
- Output → {1350;1400;1300;1200;1100;1000;1050;1250;1150}