C
CJ
How can a series of unique, whole number, random numbers be generated so that
no two numbers are alike?
no two numbers are alike?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
T. Valko
L
L. Howard Kittle
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
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
T
T. Valko
I have a super complex array formula that will do this but you should
explore other options first.
explore other options first.
C
CJ
Thank you. I explored both ideas and now I have a very useful tool. Wonderful
C
CJ
Really great help, thank you. I now have a very useful tool.
Wonderful.
Wonderful.