Generate Unique Random Numbers

The RANDBETWEEN function provides a non-dynamic array solution to creating a set of random numbers. It returns an integer between the minimum and maximum values you specify. The problem is, however, it is likely to return duplicates after each worksheet recalculation. (See Example 1)

Example 2 combines SORTBY, SEQUENCE and RANDARRAY to generate a list of numbers from 1–10 without repeats. This works because SEQUENCE(10) guarantees an array of unique elements: {1;2;3;4;5;6;7;8;9;10}. These are fed into the SORTBY array argument and ordered according to RANDARRAY(10), which returns a list of 10 decimal figures from 0–1. The numbers specified in the SEQUENCE and RANDARRAY functions must match, otherwise a #VALUE! error occurs.

Example 3 displays a list of 10 numbers from 1–20. It uses the same formula as Example 2, except it’s housed in the INDEX function. In the row_num argument, SEQUENCE(10) ensures only the first 10 rows are outputted.

Image Notes

Shaded yellow cells contain formulas.

Function Syntaxes

RANDBETWEEN(bottom, top)

SORTBY(array, by_array, [sort_order],...)

SEQUENCE(rows, [columns], [start], [step])

RANDARRAY([rows], [columns], [min], [max], [integer])

INDEX(array, row_num, [column_num])