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. (𝘚𝘦𝘦 𝘌𝘹𝘢𝘮𝘱𝘭𝘦 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 𝗮𝗿𝗿𝗮𝘆 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 𝗿𝗼𝘄_𝗻𝘂𝗺 argument, SEQUENCE(10) ensures only the first 10 rows are outputted.

𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻 𝗦𝘆𝗻𝘁𝗮𝘅𝗲𝘀

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])

𝗜𝗺𝗮𝗴𝗲 𝗡𝗼𝘁𝗲𝘀

𝘚𝘩𝘢𝘥𝘦𝘥 𝘺𝘦𝘭𝘭𝘰𝘸 𝘤𝘦𝘭𝘭𝘴 𝘤𝘰𝘯𝘵𝘢𝘪𝘯 𝘧𝘰𝘳𝘮𝘶𝘭𝘢𝘴.