To generate random numbers without duplicates you can use a formula based on the SORTBY, UNIQUE and RANDARRAY functions.
How to get random numbers without duplicates?
- Select cell E3.
- Type =SORTBY(UNIQUE(RANDARRAY(n, 1, min, max, TRUE)), RANDARRAY(n))
- Here, n is the number of unique random numbers, min and max with the minimum and maximum range.
- Press Enter.
- The formula will return n unique random numbers within the specified range.
Example
In this example, we have a dataset in range C2:C4 defining parameters for generating random numbers, including the minimum value (1), the maximum value (100), and the number of unique random numbers to generate (5). The goal is to create a dynamic array formula to generate a list of random numbers without duplicates based on these parameters.
Formula:
=SORTBY(UNIQUE(RANDARRAY(C4, 1, C2, C3, TRUE)), RANDARRAY(C4))
The formula in cell E2 uses the RANDARRAY function to generate random numbers, combined with UNIQUE to ensure no duplicates, and SORTBY for ordering. The output dynamically updates to display the specified number of unique random numbers within the defined range.
Explanation
Here is a detailed breakdown of the formula:
RANDARRAY(C4, 1, C2, C3, TRUE): This formula generates a random array of numbers. The RANDARRAY function uses the following arguments:
- C4: Specifies the number of rows for the random numbers (e.g., 5).
- 1: Indicates the array has one column.
- C2 and C3: Define the minimum (1) and maximum (100) values for the random numbers, respectively.
- TRUE: Ensures the random numbers are integers.
The formula creates a vertical array with 5 random integers between 1 and 100 (inclusive).
UNIQUE(RANDARRAY(…)) ensures that the numbers in the array are unique and filters out any duplicate numbers generated by RANDARRAY. The output is a unique list of random integers.
RANDARRAY(C4) generates a second array of random numbers (not constrained to specific min/max or integers). C4 specifies the number of rows for the random numbers (e.g., 5). The formula returns a random array used to sort the unique numbers. For example: {0.45; 0.82; 0.12; 0.67; 0.33}
SORTBY(UNIQUE(…), RANDARRAY(C4)) sorts the unique random integers using a randomly generated second array as the sort key using a following steps. UNIQUE(…) generates the unique random integers (e.g., {55; 93; 13; 9; 75}). RANDARRAY(C4) generates a corresponding array of random numbers as the sorting criteria. SORTBY rearranges the unique integers in the order defined by the random sort key. The result is a shuffled version of the unique integers, for example: {93; 55; 9; 75; 13}
If sorting is not needed you generates unique random numbers without shuffling their order:
=UNIQUE(RANDARRAY(C4, 1, C2, C3, TRUE))
To sort the unique random numbers in ascending order:
=SORT(UNIQUE(RANDARRAY(C4, 1, C2, C3, TRUE)))
Generate unique random numbers with the SEQUENCE function
In this example, we have a dataset defined by a minimum value of 1, a maximum value of 100, and a step size of 3, specified in cells C2, C3, and C4 respectively. The goal is to generate 5 random numbers from this dataset that are evenly spaced based on the step size and display them in column E.
Formula:
=INDEX(SORTBY(SEQUENCE(ROUNDUP((C3-C2+1)/C4,0),1,C2,C4), RANDARRAY(ROUNDUP((C3-C2+1)/C4,0))),SEQUENCE(C5))
The formula in cell E2 combines SEQUENCE, ROUNDUP, and RANDARRAY functions to create an evenly spaced sequence, randomize it, and select the specified number of unique outputs. The result is a dynamically sorted and randomized set of numbers fitting the criteria outlined in the input parameters.
Explanation:
First, prepare the input for the SEQUENCE function. The ROUNDUP function ensures that any fractional results are rounded up to the next whole number. The expression inside ROUNDUP is (C3-C2+1)/C4, where C3 is the Max value (100), C2 is the Min value (1) and C4 is the Step size (3). This part of the formula determines how many steps or elements are needed in the sequence.
The SEQUENCE function generates an array of numbers in a defined order. The first SEQUENCE is defined as SEQUENCE(ROUNDUP((C3-C2+1)/C4, 0), 1, C2, C4).
The rows argument calculates the number of elements in the sequence using the formula ROUNDUP((C3-C2+1)/C4, 0), resulting in 34. The “columns” argument sets the output to a single column by specifying 1. The “start” argument defines the sequence’s starting point as C2 (minimum value, 1). The “step” argument determines the increment between numbers in the sequence, defined as C4 (step size, 3).