UNIQUE function not as expected

%3CLINGO-SUB%20id%3D%22lingo-sub-2669869%22%20slang%3D%22en-US%22%3EUNIQUE%20function%20not%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2669869%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20generate%20an%2011x4%20array%20of%20unique%20integers.%20UNIQUE(RANDBETWEEN(1%2C44))%20will%20generate%20the%20array%20but%20repeats%20numbers.%20Why%20doesn't%20UNIQUE%20work%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2669869%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670802%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20function%20not%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1133413%22%20target%3D%22_blank%22%3E%40JoeNews%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERANDBETWEEN()%20returns%20scalar%20value%2C%20perhaps%20you%20mean%20RANDARRAY().%20Yes%2C%20it%20repeats%20numbers%2C%20to%20be%20sure%20you%20have%2044%20of%20them%20unique%20just%20generate%20big%20enough%20random%20array.%20That%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(UNIQUE(RANDARRAY(1000%2C1%2C1%2C44%2C1))%2CSEQUENCE(11%2C4))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20only%20it%20could%20give%20from%20time%20to%20time%20%23SPILL!%20error%2C%20that's%20nothing%20to%20do%20with%20that%20but%20recalculate%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DiaJv6E0GRC4%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20Spill%20Error%20Volatile%20Size%20-%20Episode%202315%20-%20YouTube%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670919%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20function%20not%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1133413%22%20target%3D%22_blank%22%3E%40JoeNews%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20standard%20way%20of%20getting%20unique%20values%20is%20to%20generate%20them%20first%20and%20then%20randomise%20their%20order.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20index%2C%20%20SEQUENCE(44)%2C%0A%20%20%20%20random%2C%20RANDARRAY(44)%2C%0A%20%20%20%20seq%2C%20%20%20%20SEQUENCE(11%2C4)%2C%0A%20%20%20%20sorted%2C%20SORTBY(index%2C%20random)%2C%0A%20%20%20%20INDEX(sorted%2C%20seq)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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

@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.