Aug 20 2021 09:14 AM
Trying to generate an 11x4 array of unique integers. UNIQUE(RANDBETWEEN(1,44)) will generate the array but repeats numbers. Why doesn't UNIQUE work here?
Aug 20 2021 03:04 PM
RANDBETWEEN() returns scalar value, perhaps you mean RANDARRAY(). Yes, it repeats numbers, to be sure you have 44 of them unique just generate big enough random array. That could be like
=INDEX(UNIQUE(RANDARRAY(1000,1,1,44,1)),SEQUENCE(11,4))
The only it could give from time to time #SPILL! error, that's nothing to do with that but recalculate Excel Spill Error Volatile Size - Episode 2315 - YouTube
Aug 20 2021 04:22 PM
SolutionA standard way of getting unique values is to generate them first and then randomise their order.
= LET(
index, SEQUENCE(44),
random, RANDARRAY(44),
seq, SEQUENCE(11,4),
sorted, SORTBY(index, random),
INDEX(sorted, seq)
)
Aug 22 2021 08:51 AM
Aug 20 2021 04:22 PM
SolutionA standard way of getting unique values is to generate them first and then randomise their order.
= LET(
index, SEQUENCE(44),
random, RANDARRAY(44),
seq, SEQUENCE(11,4),
sorted, SORTBY(index, random),
INDEX(sorted, seq)
)