Random Numbers – Unique

Topic

New Reply

Hi To All,

For a set of thirty samples I must construct a “random order” in which to run tests. The numbers to be randomized are 1,2,3,4,……36, and of course each number must be unique, cannot be used more than once. Do in need VBA to do this or is there a easier formula.

Thanks for your help….great resource

Marty

Reply | Quote

Replies

Marty,

I don’t know if this can be done w/o VBA but here is a cut & paste ready solution.

Upon further inspection this solution may take some minor editing for your purposes but it has the basics.

Call: =RandLotto(1, 36, 36)
This will return your 36 random numbers as a string in the cell you place the formula in.

This routine could be easily modified to place the numbers in separate cells. Let me know if you need help.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Why not put the numbers 1 to 36 in one column
Then the random function in a second which you can then convert to values (they will be unique)
Then sort the list based upon the rand column

If you want it totally random leave the rand() column as a function

Reply | Quote

they will be unique

Andrew,

I seem to remember Excel having a problem with Rand not returning consistent unique numbers – although this was while I was still working – 10 yrs ago. Has that been fixed?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

very elegant!

Reply | Quote

Good Morning Andrew and RG,

Wow, ask and you shall receive…..both will do the job. As usual this site exceeds expectations, an excellent learning tool for all. I’m coming to the VBA table a little late but is powerful stuff

Thanks Again,
Marty

Reply | Quote

I believe it was meant to be fixed in one of the 2003 Service Packs.
To be honest, with 36 values it is unlikely to be an issue even if it wasn’t.

There was an issue with Random numbers being generated which I think was fixed way back.
It also needed a lot of recalcs on a lot of cells.

MS have this article on the subject Random Nos MS

Reply | Quote

I’ve found the random number functions available from http://www.ntrand.com/ to be very powerful (and FREE!!). Richard.

Reply | Quote