[Solved] Generate Random Unique Integers

[MathHawk]

I've been trying for years to generate a list of unique random integers. Many people said it could not be done, others were stumped. Since I finally figured out a way to do it, I thought I would share since I have never found a solution that doesn't use a separate macro or script and involved multiple steps. I was always interested in something that could instantly re-generate without a great deal of effort.

In this example, I used randbetween(), but you could also use a rand() function to generate automatically without manually re-generating with ctrl shift f9.

This can, of course, scale down for smaller lists. I usually only need a few random numbers at a time, but I thought I would scale up to see if my processor could handle it. It did bog down a bit when I re-generate, but it only took a few seconds.

Comments/Questions?

[Villeroy]

Much easier:
Create a range of =RAND() as random seed.
Get the rank of each value: =RANK(SeedRange;SeedRange) [entered as array formula with Ctrl+Shift+Enter]
This creates an integer sequence of 1 ... Count(SeedRange). This may be used to pick random but unique values from a third range using the INDEX function.

[MathHawk]

I have tried that in the past but it is unreliable due to ranking issues where two rand() numbers are equal. And it happens more often than you would expect. I wanted something that didn't require checking to make sure there were no errors every time the sets of random numbers were generated.

[djryanash]

I've been trying for years to generate a list of unique random integers. Many people said it could not be done, others were stumped. Since I finally figured out a way to do it, I thought I would share since I have never found a solution that doesn't use a separate macro or script and involved multiple steps. I was always interested in something that could instantly re-generate without a great deal of effort.

In this example, I used randbetween(), but you could also use a rand() function to generate automatically without manually re-generating with ctrl shift f9.

This can, of course, scale down for smaller lists. I usually only need a few random numbers at a time, but I thought I would scale up to see if my processor could handle it. It did bog down a bit when I re-generate, but it only took a few seconds.

Comments/Questions?

Thanks for this - it's awesome!!!

Can you briefly explain how it works? It's a bit beyond my ken.

[MrProgrammer]

I've been trying for years to generate a list of unique random integers.

[Tutorial] Randomization in Calc, topic C