Prev: Can RGB fill color be automated based on 3 cell values?
Next: Help with lookup and formulas
From: CJ on 10 Feb 2010 00:52
How can a series of unique, whole number, random numbers be generated so that
no two numbers are alike?
From: T. Valko on 10 Feb 2010 01:04
See this:

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

--
Biff
Microsoft Excel MVP


"CJ" <CJ(a)discussions.microsoft.com> wrote in message
news:A0D0B55E-1C33-44D5-B5AC-81AB192CA78C(a)microsoft.com...
> How can a series of unique, whole number, random numbers be generated so
> that
> no two numbers are alike?


From: L. Howard Kittle on 10 Feb 2010 01:24
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" <CJ(a)discussions.microsoft.com> wrote in message
news:A0D0B55E-1C33-44D5-B5AC-81AB192CA78C(a)microsoft.com...
> How can a series of unique, whole number, random numbers be generated so
> that
> no two numbers are alike?


From: T. Valko on 10 Feb 2010 02:44
I have a super complex array formula that will do this but you should
explore other options first.

--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:uWSjobhqKHA.3408(a)TK2MSFTNGP06.phx.gbl...
> See this:
>
> http://mcgimpsey.com/excel/udfs/randint.html
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "CJ" <CJ(a)discussions.microsoft.com> wrote in message
> news:A0D0B55E-1C33-44D5-B5AC-81AB192CA78C(a)microsoft.com...
>> How can a series of unique, whole number, random numbers be generated so
>> that
>> no two numbers are alike?
>
>


From: CJ on 10 Feb 2010 04:11
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" <CJ(a)discussions.microsoft.com> wrote in message
> news:A0D0B55E-1C33-44D5-B5AC-81AB192CA78C(a)microsoft.com...
> > How can a series of unique, whole number, random numbers be generated so
> > that
> > no two numbers are alike?
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Can RGB fill color be automated based on 3 cell values?
Next: Help with lookup and formulas