How To Use Randbetween In Excel Without Duplicates?

Using RANDBETWEEN alone cannot prevent duplicates, as the function is designed to select a new random number from a given range every time the worksheet is recalculated. However, you can use RANDBETWEEN in combination with other functions or methods to generate a list of unique random numbers, regardless of your Excel version.

Method 1: The Modern Dynamic Array Method (Excel 365 or 2021)

For users with Excel for Microsoft 365 or Excel 2021, dynamic array formulas provide the most efficient, single-cell solution for generating a unique list of random numbers.

The formula and how it works

A dynamic array formula combines the SORTBY, RANDARRAY, and SEQUENCE functions to return a unique, randomly sorted list.

Formula:=SORTBY(SEQUENCE(max-min+1,,min), RANDARRAY(max-min+1))

• SEQUENCE(max-min+1,,min): Generates a sequential array of all possible numbers within your desired range.
  • max-min+1 determines the total count of numbers in the sequence.
  • min specifies the starting number of the sequence.
• RANDARRAY(max-min+1): Creates an array of random decimal numbers. The size of this array matches the sequence, providing a random sorting key for each number.
• SORTBY(...): Sorts the sequential array generated by SEQUENCE using the random array created by RANDARRAY as the sorting order. This shuffles the numbers into a unique, random list.

Step-by-step example: 10 unique numbers from 1 to 100

1. Select a cell where you want the list to begin (e.g., A1).
2. Type the formula: =SORTBY(SEQUENCE(100-1+1,,1), RANDARRAY(100))
3. Press Enter.
4. The formula will "spill" a list of 100 unique random numbers into the cells below.

Important considerations

• Volatile function:RANDARRAY is a volatile function, meaning the list will change every time you make a change in the workbook. To fix the numbers, select the list, copy it (Ctrl + C), and then paste as values (Alt + E + S + V or right-click and choose Paste Special > Values).
• Select a smaller list: To get a smaller sample from the sorted array, you can wrap the formula in the TAKE function. For example, to get 10 unique numbers from 1 to 100, use =TAKE(SORTBY(SEQUENCE(100,,1), RANDARRAY(100)),10).

Method 2: The Helper Column Method (All Excel Versions)

If you don't have dynamic array functions, the classic helper column method is a reliable alternative. It works by assigning a random number to each item in a list and then sorting the list based on the random values.

Step-by-step example: Randomize a list of names

Let's say you have a list of names in column A (A2:A11) and need to assign a unique random number to each person.

1. Create a helper column. In the cell next to your first item (e.g., B2), enter the formula =RAND(). RAND is a volatile function that produces a random decimal between 0 and 1.
2. Copy the formula. Drag the fill handle (the small square at the bottom-right corner of the selected cell) down to apply the formula to the rest of the cells in the helper column (e.g., B2:B11).
3. Freeze the values. Since RAND is volatile, the numbers will change constantly. To freeze them, select the helper column (B2:B11), copy it (Ctrl + C), and then paste as values (Alt + E + S + V) in the same location. The formulas are now replaced by their static values.
4. Sort the data. Select both your original data (A2:A11) and the helper column (B2:B11). Go to the Data tab and click Sort.
5. Specify sort criteria. In the Sort dialog box, choose the helper column as the "Sort by" column and select a sort order (e.g., Smallest to Largest). Click OK. Your original list is now in a new, unique random order.
6. Delete the helper column. Once the sort is complete, you can delete the helper column.

Method 3: The RANK-based Helper Column Method (All Excel Versions)

This method also uses a helper column but avoids the extra step of sorting, though it still requires a copy-paste as values step.

Step-by-step example

1. Generate a list of random numbers. In column B, starting in B2, enter =RANDBETWEEN(1,1000) (adjust the range as needed). Copy this down for the desired number of cells.
2. Generate a unique list using RANK.EQ. In column C, starting in C2, enter the formula =RANK.EQ(B2,$B$2:$B$11,0). This formula ranks each random number in column B. Since there's a very low chance of RANDBETWEEN producing the exact same number, the ranks will be unique.
3. Freeze the values. As with the previous method, the formulas are volatile. Select column C, copy, and paste as values in the same location.
4. Delete the helper columns. After pasting as values, you can remove columns B and C.

Handling duplicate RANDBETWEEN values

While unlikely, RANDBETWEEN could produce a duplicate. To account for this, a more complex version of the RANK.EQ formula exists, which adds a small increment to differentiate identical numbers.

Modified RANK.EQ formula:=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

This formula:

• Finds the rank of the current value.
• Adds a count for any preceding identical values, ensuring each duplicate gets a distinct rank.

Comparison of methods