• Status: Solved
• Priority: Medium
• Security: Public
• Views: 493

# Generate unique random numbers

Hi,

I need something that will generate a column full of random numbers between say 1 and 50, which are unique, the number of rows to include needs to be dynamic and would prefer a formula as need to avoid vba.

Cheers
0
StormFusion
1 Solution

Commented:
=RAND()*50

Put this in the range you want it to be in. Everything you hit F9, it updates.

If you want only integer number, use = Int(Rand()*50)

This might not be unique though especially if you were looking for integers.
0

Commented:
There isn't a built in fuction that will do the unique part (RAND() is not unique).  However, I did find a script that will do unique - http://www.ozgrid.com/VBA/RandomNumbers.htm
0

Commented:
Jim,

Indeed my solution gives no unique list, as mentioned, however, he mentioned to want to avoid vba. But your looks nice.

BTW: do you know Antrat, the OzGrid guy? (Dave). We used to spend masses of time together here :-)

0

Commented:
1) List the numbers 1 through 50 in A1:A50
2) Put the following formula in cells B1:B50
=RAND()
3) To pull a set of umpteen unique random numbers, use the following formula in cell C1 then copy it down:
=INDEX(\$A\$1:\$A\$50,MATCH(SMALL(\$B\$1:\$B\$50,ROWS(\$C\$1:\$C1)),\$B\$1:\$B\$50,0))

Each time you hit F9, you get a different selection of random numbers

For grins, the sample workbook highlights any duplicates.

UniqueRandomNumbersQ26418995.xlsx
0

Commented:
Quick and dirty:
http://www.random.org/integers/?num=50&min=1&max=50&col=1&unique=on&base=10&format=html&rnd=new

You can decide how many columns you want it formatted in, start number, finish number.  Easier interface than Excel.
0

Author Commented:
Perfect, simple and effectively clever, cheers
0

Commented:
StormFusion,
Thanks for the kind words and grade!