SOLVED

UNIQUE function not as expected

Copper Contributor

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

@JoeNews 

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

best response confirmed by allyreckerman (Microsoft)
Solution

@JoeNews 

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.
Thank you Sergei. Works great. Thanks for your help.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@JoeNews 

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

View solution in original post