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) )
4 Replies
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) )- Thank you Peter. Works beautifully.
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.
