How to generate a set of unique random numbers in Excel

There are many reasons for generating random numbers. You might want to test a financial model, create sample data, or simulate sports fixtures.

Excel has three functions for this purpose: RAND, RANDARRAY, and RANDBETWEEN.

However, what if you wanted to generate a set of unique random numbers? Unfortunately, none of them is capable of doing this on their own.

Let's take a look at the three examples in the video.

𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟭 (𝘄𝗶𝘁𝗵 𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀)

RANDBETWEEN returns an integer between 1 and 10. By copying the formula down 10 rows, a list is created. However, each formula is independent, so repetition is highly likely after each workbook calculation (F9).

𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟮 (𝘄𝗶𝘁𝗵𝗼𝘂𝘁 𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀)

A list of 10 unique numbers from 1 to 10 is generated using a combination of SORTBY, SEQUENCE, and RANDARRAY.

SEQUENCE(10) guarantees an array of unique elements: {1;2;3;4;5;6;7;8;9;10}, and SORTBY orders these according to the 10 decimal values from 0 to 1 that RANDARRAY produces. The values given in SEQUENCE and RANDARRAY must match; otherwise, a #VALUE error occurs.

𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟯 (𝘄𝗶𝘁𝗵𝗼𝘂𝘁 𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀)

This is based on the same formula as Example 2, except it's housed in INDEX to allow for greater flexibility with the range of possible numbers (1–50) whilst limiting the output to 10 with SEQUENCE.

#excel #microsoftexcel #globalexcelsummit

---

The Global Excel Summit is the world's largest virtual gathering of Microsoft Excel users and experts.

Learn more at https://globalexcelsummit.com/.

Find us on:

👔 LinkedIn: LinkedIn: global-excel-summit
🐤 Twitter: Twitter: ExcelSummit
🟦 Facebook: Facebook: globalexcelsummit
📸 Instagram: Instagram: globalexcelsummit

• #education
• #excel