How to Generate Random Numbers in Excel Without Duplicates
In this article, we will show you how to generate random numbers in Excel without duplicates within a range. Simply follow the steps below.
Random Number Generator With No Duplicates in Excel
Random numbers without duplicates can be generated in Excel using two effective methods: the ‘Helper Columns and Ranking’ technique and ‘Advanced Array Formulas’. We will discuss how to use each method in the following sections.
Method 1: Using Helper Columns and Ranking
To generate random numbers in Excel without duplicates, you can use helper columns and ranking. Here’s how to do it:
1. Set Up a Helper Column for Generating Random Decimals
In our example, we have a list of countries in Column A, each paired with their capitals in Column B. Let’s generate random numbers without duplicates. This example is suitable for educational purposes or simple quizzes.
In cell A2, type `=RAND()`. This function generates a random decimal between 0 and 1. Extend this formula down to A11 by clicking on the lower right corner of cell A2 and dragging down to cell A11. This setup ensures each row will have its own random decimal value that is adjacent to the capital list starting in cell B2.
2. Use Ranking to Assign Unique Identifiers to Random Decimals
Enter `=RANK.EQ(A2,$A$2:$A$11,1)` in cell C2.
Here’s what each part of the formula means:
A2: The cell whose rank you want to find within the specified range.
$A$2:$A$11: The range of cells against which the value in A2 is compared to determine its rank.
1: This last argument determines the order of the ranking. A value of 1 indicates that the ranking should be done in ascending order, meaning that the smallest value in the range $A$2:$A$11 gets the rank of 1, the next smallest value gets the rank of 2, and so on up to the largest value.
Copy this formula from C2 down to C11 by dragging the fill handle. This step assigns each capital a unique identifier, which correlates to its position in a randomized list.
3. Convert Ranked Numbers to Static Values to Avoid Recalculation
Select the range C2 to C11, copy it by pressing Ctrl+C, right-click on the selected range, and choose ‘Paste Special’ followed by ‘Values’ from the submenu. This action replaces the formulas in the cells with their current values, thus freezing the numbers and preventing any change when the worksheet recalculates.
4. Ensure Accuracy and Uniqueness in the Output
Review the numbers in column C to confirm that each number from 1 to 10 is used exactly once and corresponds to a different capital. This verification ensures that your random selection is both unique and complete, covering all listed capitals without repetition.
Method 2: Using Advanced Array Formulas
Follow the steps below to generate random numbers in Excel without duplicates using advanced array formulas.
1. Prepare the Output Range for Random Number Generation
We will use a dataset that contains the countries in Column A and the capitals in Column B. Select the cells D2 to D11. This range will hold the random numbers that correspond to the capitals listed in column B. Preparing this range ensures that the random numbers will be directly adjacent to their associated capitals.
2. Enter an Advanced Array Formula to Ensure Non-Repeating Selections
Type the following formula into the formula bar while cells D2 to D11 are selected:
=LARGE(ROW($1:$10)*NOT(COUNTIF($D$1:D1, ROW($1:$10))), RANDBETWEEN(1,10+1-ROW(D1)))
Where:
ROW($1:$10) generates an array of numbers from 1 to 10.
NOT(COUNTIF($D$1:D1, ROW($1:$10))) ensures that numbers that have already been used are not considered again.
LARGE(…, RANDBETWEEN(1, 10+1-ROW(D1))) picks a random number from the remaining available numbers in the array.
Then, press Ctrl+Shift+Enter to enter it as an array formula. This complex formula works by excluding numbers that have already been generated.
3. Apply the Formula Across the Selected Range to Cover All Entries
If the formula does not automatically fill down through D11, manually drag the fill handle from D2 to D11. This manual extension is necessary to apply the unique random number generation to each cell in the selected range.
4. Verify the Uniqueness and Correctness of All Generated Numbers
Inspect the numbers in column D to ensure that every number between 1 and 10 appears once and only once. This final check confirms that the array formula has correctly produced a non-repeating sequence of numbers, each linked to a unique world capital.
We hope that you now have a better understanding of how to generate random numbers in Excel without duplicates within a range. If you enjoyed this article, you might also like our article about using Excel conditional formatting duplicates or our article on how to count the number of duplicates in Excel.
