Unique random numbers refer to random numbers without duplicates. Generating such numbers is straightforward in Excel, thanks to dynamic array formulas. These numbers can be used for tasks like random sampling, creating unique IDs, testing data, and more.

Here’s how to generate unique random numbers in Excel with a formula.

Dynamic Array Formula for Unique Random Numbers

Use the following formula to generate unique random numbers in Excel (random numbers without duplicates):

=CHOOSEROWS(
   SORTBY(SEQUENCE(total_n, 1, start_at), RANDARRAY(total_n), 1), 
   SEQUENCE(required_n)
)

Parameters:

• total_n: The total number of numbers in the sequence.
• start_at: The starting number of the sequence.
• required_n: The count of unique random numbers to generate.

Example:

To generate 5 unique random numbers between 1 and 10:

• total_n = 10
• start_at = 1
• required_n = 5

Example: Generating 10 Four-Digit Random Numbers

If you want to generate 10 unique four-digit random numbers:

• Generate a sequence of numbers from 1000 to 1099 (a total of 100 numbers, though you can expand this range as needed).
• Randomly pick 10 unique numbers from this list.

Formula:

=CHOOSEROWS(
   SORTBY(SEQUENCE(100, 1, 1000), RANDARRAY(100), 1), 
   SEQUENCE(10)
)

Note: This formula is specific to Excel. It will not work in Google Sheets because Google Sheets does not support the SORTBY function. For a detailed comparison, refer to this resource: SORT and SORTBY – Excel Vs Google Sheets

Formula Breakdown

Part 1: Generating and Randomizing Numbers

SORTBY(SEQUENCE(100, 1, 1000), RANDARRAY(100), 1)

1. SEQUENCE(100, 1, 1000):
   • Generates 100 consecutive numbers starting from 1000.
   • Output: {1000; 1001; 1002; ...; 1099}.
2. RANDARRAY(100):
   • Generates 100 random numbers between 0 and 1 (used for sorting).
3. SORTBY(SEQUENCE(100, 1, 1000), RANDARRAY(100), 1):
   • Sorts the sequence {1000; 1001; ...; 1099} by the random numbers generated by RANDARRAY.
   • Produces a shuffled array of 100 unique numbers from 1000 to 1099.

Part 2: Selecting n Random Numbers

CHOOSEROWS(..., SEQUENCE(10))

1. SEQUENCE(10):
   • Creates a sequence from 1 to 10 to select the first 10 rows from the shuffled array.
2. CHOOSEROWS:
   • Extracts the first 10 numbers from the randomized list.
   • Output: A list of 10 unique, random four-digit numbers.

Why Use This Formula?

This formula is highly versatile and offers several advantages. It supports dynamic updates, meaning that any changes to the parameters—such as the total number of values (total_n), the starting point (start_at), or the required number of outputs (required_n)—automatically update the results.

Additionally, the formula ensures there are no duplicates, guaranteeing that all numbers in the output are unique.

Its flexibility allows you to generate numbers within any range or count, making it suitable for both small lists and large datasets.

Common Applications

Random sampling involves selecting unique samples from a dataset, making it a common use case for generating unique random numbers. In testing scenarios, these numbers are often used to create randomized test data for experiments or software testing. They are also valuable for generating distinct IDs for database records, ensuring that each entry has a unique identifier.

Additionally, unique random numbers play a key role in lottery simulations, where they are used to create randomized number draws for fair and unbiased outcomes.

This approach ensures robust, efficient generation of unique random numbers in Excel, suitable for diverse real-world applications.