New Contributor

# 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?

4 Replies

# Re: UNIQUE function not as expected

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

# Re: UNIQUE function not as expected

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)
)``````

# Re: UNIQUE function not as expected

Thank you Peter. Works beautifully.

# Re: UNIQUE function not as expected

Thank you Sergei. Works great. Thanks for your help.