Generating Random Numbers - Tough One

[Legacy 5232]

Hi Everyone

I need to generate about 2695 random numbers in column A that are 7 digits long, between 1000000:9999999, do not start with zero, and are not duplicates of each other. Does anyone know the formula?

Thanks
Jacob

 

[Legacy 12630]

Here's a macro that makes use of worksheet functions/tools and can easily be created via the macro recorder. Activate a blank worksheet before running it :-

Sub Create_2695_Unique_7Digits_Nbrs()
Application.ScreenUpdating = False
[A1] = 1000
[A1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=9999
[B1:B9000].FormulaR1C1 = "=RAND()"
[B1:B9000] = [B1:B9000].Value
Columns("A:B").Sort Key1:=[B1], Header:=xlNo
[C1:C2695].FormulaR1C1 = "=RANDBETWEEN(1,999)"
[C1:C2695] = [C1:C2695].Value
[D1:D2695].FormulaR1C1 = "=RC[-3]*1000+RC[-1]"
[D1:D2695] = [D1:D2695].Value
Columns("A:C").Delete
End Sub

The run time is about 1 second.

Tusharm's macro using an array is prettier but, of course, requires knowledge of VBA.
( Has it been tested? I think Rand() should be Rnd() )

 

[Emily]

Or you can try this code, it takes less than 15 seconds for my 800Mhz PC

Sub RandomGen()
For i = 1 To 2695
Do
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
num = Int(9000000 * Rnd + 1000000)

If Range("A1:A2695").Find(num, LookAt:=xlWhole) Is Nothing Then Exit Do
Loop
Cells(i, 1) = num
Next i
End Sub

 

[Gary Drumm]

I'm looking for a modified version of Emily's solution

I need a macro that will generate random (unique) numbers, but with code that stops producing numbers at the last active row (there will be no blank rows above the last row).

I also need for that unique number to be attached to that row of data if it is moved to another sheet in the same workbook.

Hope that makes sense.
Thanks,
Gary

 

[tusharm]

Re: I'm looking for a modified version of Emily's solution

Have you looked at the options detailed at
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

I need a macro that will generate random (unique) numbers, but with code that stops producing numbers at the last active row (there will be no blank rows above the last row).

I also need for that unique number to be attached to that row of data if it is moved to another sheet in the same workbook.

Hope that makes sense.
Thanks,
Gary

 

[Gary Drumm]

Tushar,
I'm afraid these examples won't work for what I need.
Or perhaps they will, but I just can't see how.
It appears they generate random numbers from a pre-existing set of numbers. What I need is a way to create unique 7 digit numbers from nothing, and have them permenantly assigned to the data that follows on the same row that they occupy. I also need the other requirements I listed in my post, in order to add the flexability I need.
Thanks for the link anyway.
Gary

 

[jindon]

try

Sub Sample
Dim x As Long
Randomize
With CreateObject("Scripting.Dictionary")
    Do
Again:
        x = Int(Rnd * 9000000) + 100000
        If .exists(x) Then GoTo Again
        .add x, Nothing
    Loop While .Count <= 2695
Range("a1").Resize(.Count) = Application.Transpose(.keys)
End With
End Sub

 

[Gary Drumm]

jindon,
Thanks for the fast reply. I copied, pasted, and ran your macro, and every row in column A shows 0, except for A1; which on successive tries shows random numbers in A1 only. Ever other row from A2 thru A84 remained a 0 (zero).

Perhaps I should try posting a sample of my spreadsheet?
Gary

 

[jindon]

I have modified the code right after I have posted.

Do you want to copy the code and try it again ?

 

[jindon]

OOps

I have changed Again!!!

Sory try again please...

 

[Gary Drumm]

Yes,
I would like to try again.
Please post the updated code.
Thank you very much for your help