SOLVED

# Who can explain this to me?

Occasional Contributor

# Who can explain this to me?

Who can explain this to me?

I used the formula =UNIQUE(RANDBETWEEN(9788723527370120, 9788723527370990))DBETWEEN(9788723527370120, 9788723527370990))

10 Replies

# Re: Who can explain this to me?

This number should not have come twice if the range in the Unique formula had been between say A1 to A500. You are using Unique in each cell. That is why they can repeat in the list.

# Re: Who can explain this to me?

Do you have ant idea on how to solve this? i need a list with unique numbers within this range.
best response confirmed by Ohayonson (Occasional Contributor)
Solution

# Re: Who can explain this to me?

Let's say you want 100 random numbers, but list only the unique values:

=UNIQUE(RANDARRAY(100,1,9788723527370120,9788723527370990,TRUE))

Warning: keep in mind that ALL numbers will end in 0 because your numbers have 16 digits but Excel only displays 15 significant digits. Why not generate random numbers between 120 and 990?

# Re: Who can explain this to me?

@OhayonsonSee attached. You should apply Unique in column B not A.

# Re: Who can explain this to me?

@jitinm Thank you very much!

# Re: Who can explain this to me?

The reason why i need this long number is because i need a nubmer which is similar to an isbn nr. with the prefix 9788723527370 but i cam make it shorter so the prefix is 978872352737.
Thank you very much for your time and answer.

# Re: Who can explain this to me?

I get a spill error when i paste

=UNIQUE(RANDARRAY(100,1,9788723527370120,9788723527370990,TRUE))

in a clean sheet at a1 cell.

Am i doing something wrong?

# Re: Who can explain this to me?

No, it appears to be a bug.

Paste the formula into any other cell - it should work.

After that it should work in A1 too.

# Re: Who can explain this to me?

That's floating error which could appear could not #SPILL! - Volatile Size (microsoft.com) Maybe considered as bug, but that's how dynamic arrays are designed.

Thanks, Sergei!