Forum Discussion
Solved
UNIQUE function not as expected
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?
A 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) )
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
Thank you Sergei. Works great. Thanks for your help.