Random Number without duplicates

Posted by Jim L on January 30, 2001 10:17 AM

I'd like to generate a series of 36 random
numbers without duplicates. The 36 random
numbers begin at #1 and end at #36.

Any answers for this one?
Thanks for the help.

Posted by Mark W. on January 30, 2001 11:33 AM

1. Enter the formula, =RAND(), into cells A1:A36.
2. Enter the formula, =RAND(A1,$A$1:$A$36), into
cell B1, and copy down.

Posted by Mark W. on January 30, 2001 11:52 AM

Oops -- Another Typo!!

Step #2 should read:

2. Enter the formula, =RANK(A1,$A$1:$A$36), into
cell B1, and copy down.

Posted by Dave Hawley on January 31, 2001 3:27 AM

Hi Jim

Here's some code for 1. Run it a sheet not using A1:F6.

Sub RandomNumber()
Dim i As Integer, ii As Integer
Range("A1:F6").Clear
Randomize ' Initialize random-number generator.
For ii = 1 To 6
For i = 1 To 6
Cells(i, ii) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:F6"), Cells(i, ii)) = 1
Cells(i, ii) = Int((36 * Rnd) + 1)
Loop

Next i
Next ii
End Sub

Hope this helps

Dave

OzGrid Business Applications