Explanation
In this example, the goal is to generate a list of random numbers without duplicates.
Background study
RANDARRAY option
TheRANDARRAY functionmakes it easy to generate a list of random integers.
This formula works fine.
By increasing the range of numbers generated, we can reduce the possibility of duplicates substantially.
However, because the UNIQUE function willremoveduplicates if they exist, the final count of numbers returned will change.
The result is a list of the 15 numbers between 1-15, sorted randomly.
This guarantees a specific number of unique values across a large range of possibilities.
TheSEQUENCE functionis configured to generate 1000 numbers starting at 10000.
Thestepargument is given as 10 to return numbers that are multiples of 10.
The result from SEQUENCE is anarraythat contains 1000 numbers.
TheRANDARRAY functionis set to output 1000 numbers.
By default, RANDARRAY will return an array of long decimals.
The first 10 numbers in the array will look something like this:
These numbers are provided toSORTBYas theby_array1argument.
The numbers are guaranteed to be unique because the original array created by SEQUENCE contains no duplicates.
Random names
The approach described above can be applied to other related problems.
Legacy Excel
Dynamic array formulas are a new feature in Excel.
The final result is 10 random numbers between 1-100 in F5:F14 without duplicates.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
The range or array used to sort does not need to appear in results.
RANDARRAY Function
The Excel RANDARRAY function generates an array of random numbers between two values.
The size or the array is specified byrowsandcolumnsarguments.
The generated values can be either decimals or whole numbers.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
The MATCH function is often used together with INDEX to provide row and column numbers….
