Unique Random Numbers
 

How can a series of unique, whole number, random numbers be generated so that
no two numbers are alike?

Unique Random Numbers
 

See this:

http://mcgimpsey.com/excel/udfs/randint.html

--
Biff
Microsoft Excel MVP


"CJ" wrote in message
...
How can a series of unique, whole number, random numbers be generated so
that
no two numbers are alike?

Unique Random Numbers
 

Hi CJ,

I do not remember where I got this or if I actually wrote it, appoligies to
the author if not me.

Produces three Columns with 20 unique random numbers between:

Column A 1 to 32
Column B 1 to 64
Column C 1 to 96

Probably will need to adjusted to your exact needs. You may not want three
columns and of course the "c.Value = Int((32 * Rnd) + 1)" can be adjusted to
your desired range.

Copy and paste in the VB editor and assign to a button or key stroke.

'Option Explicit
Sub ClearAndCall90()
Range("A1:C21").ClearContents
Range("G1").Select
sonic1to32
End Sub

Sub sonic1to32()
Dim FillRange As Range
Set FillRange = Range("A1:A20")
For Each c In FillRange
Do
c.Value = Int((32 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to64
End Sub

Sub sonic1to64()
Dim FillRange As Range
Set FillRange = Range("B1:B20")
For Each c In FillRange
Do
c.Value = Int((64 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to96
End Sub

Sub sonic1to96()
Dim FillRange As Range
Set FillRange = Range("C1:C20")
For Each c In FillRange
Do
c.Value = Int((96 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

Another way may be to list the whole numbers in column A, say 1 to 500. In
B1 enter =rand() and pull down for the 500 rows. Now sort using column B as
the sort column and pick the first 10, 20 or however many rows of column A
as your unique random numbers.
Select B1:A500 and hit F9 for a new sort of numbers.

HTH
Regards,
Howard

"CJ" wrote in message
...
How can a series of unique, whole number, random numbers be generated so
that
no two numbers are alike?

Unique Random Numbers
 

I have a super complex array formula that will do this but you should
explore other options first.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See this:

http://mcgimpsey.com/excel/udfs/randint.html

--
Biff
Microsoft Excel MVP


"CJ" wrote in message
...
How can a series of unique, whole number, random numbers be generated so
that
no two numbers are alike?

Unique Random Numbers
 

Thank you. I explored both ideas and now I have a very useful tool. Wonderful

"L. Howard Kittle" wrote:

Hi CJ,

I do not remember where I got this or if I actually wrote it, appoligies to
the author if not me.

Produces three Columns with 20 unique random numbers between:

Column A 1 to 32
Column B 1 to 64
Column C 1 to 96

Probably will need to adjusted to your exact needs. You may not want three
columns and of course the "c.Value = Int((32 * Rnd) + 1)" can be adjusted to
your desired range.

Copy and paste in the VB editor and assign to a button or key stroke.

'Option Explicit
Sub ClearAndCall90()
Range("A1:C21").ClearContents
Range("G1").Select
sonic1to32
End Sub

Sub sonic1to32()
Dim FillRange As Range
Set FillRange = Range("A1:A20")
For Each c In FillRange
Do
c.Value = Int((32 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to64
End Sub

Sub sonic1to64()
Dim FillRange As Range
Set FillRange = Range("B1:B20")
For Each c In FillRange
Do
c.Value = Int((64 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to96
End Sub

Sub sonic1to96()
Dim FillRange As Range
Set FillRange = Range("C1:C20")
For Each c In FillRange
Do
c.Value = Int((96 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

Another way may be to list the whole numbers in column A, say 1 to 500. In
B1 enter =rand() and pull down for the 500 rows. Now sort using column B as
the sort column and pick the first 10, 20 or however many rows of column A
as your unique random numbers.
Select B1:A500 and hit F9 for a new sort of numbers.

HTH
Regards,
Howard

"CJ" wrote in message
...
How can a series of unique, whole number, random numbers be generated so
that
no two numbers are alike?


.

Unique Random Numbers
 

Really great help, thank you. I now have a very useful tool.
Wonderful.

"CJ" wrote:

How can a series of unique, whole number, random numbers be generated so that
no two numbers are alike?