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

 

[Gary Drumm]

An attempt to post a sample of the spreadsheet

Jindon,
I tried the last revision on a new blank workbook, and it still isn't generating any random numbers. Here is an attempt to post a shot of the spreadsheet. I'm trying to get random numbers for column A only, and stop generating them at the last row which contains data. Then if a new line of data is added later, a new number will generate, as soon as any data appears on the corresponding row of column D.

This may not be possible. If I am wasting your time, please say so.
Thanks,

for 6-20-06 w jindon random numbers.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Rand#	Priority1	Priority2	TaskDescription	Dept.	Givento:	%-or-Date	Act.Hrs.	EntryDate	DateWanted	Originator	OtherNotes		Done
2	5800816	1		Makedrippansfor1,2,3,7,8,9,10,13	PlasticPrt.	Andrew	50%	6	06/06/06	06/30/06	Starner	Inprogress
3	6215667			FabricateGuardToCoverAniloxChainDriveOnPress#2	PaperPrtF	Andrew			04/20/04	04/27/04	Cannefax
4	3606062			Newrails&piecetoCupAPadlocker	ConveyorF	Andrew			05/31/06	06/15/06	Robert	Sat.orSun.
5	3717532			FabSameBaffles&InstallOnRewind,DriveSide.ToCatchOil&DirectItIntoACatchPan.	PaperPrtM	Andrew			06/03/05	06/10/05	Edgett
6	7972660			RefitDripPansToAll4Colorheads-Press15	PlasticPrtM	Andrew			03/09/06	03/16/06	Starner	Sat.orSun.
7	1126158	1		ReplacebuttonsonBaler4	SlitBaleBlk	Booker			04/26/06	ASAP	McCoy	Partsarehere
8	7846512	2		Install(2)4'lightsaboveBlanker#4	SlitBaleBlk	Booker			06/02/06	ASAP	Angel	Lightsarehere
Current

 

[Gary Drumm]

Perhaps I should explain, this one is showing random numbers, it is not the one I was testing on. It was from your previous code, which generated numbers far past the last line that had corresponding text in the adjacent row. That code worked, but would by default add numerous rows, which complicate the end use requirement.

Hope that explains...

 

[jindon]

OK
then you only need following code to the sheet module
When you add new line on col.D and only when correnponding Col.A has no value, the code adds new unique number in Col.A..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
With Target
       If .Count>1 Then Exit Sub
       If .Column <> 4 Then Exit Sub
       If Not IsEmpty(.Offset(,-3)) Then Exit Sub
       If IsEmpty(.Value) Then Exit Sub
End With
Again:
x = Int(Rnd * 900000) + 100000
If Application.CountIf(Range("a:a"),x)>0 Then GoTo Again
Application.EnableEvents = False
Target.Offset(, -3).Value = x
Application.EnableEvents = True
End Sub

 

[Gary Drumm]

Jindon,
Okay,
It is working just as you indicated. It took me a while to figure out a few things, but it is doing exactly what you said it would.

Thank you very much
Sorry for any confussion.
Gary

 

[jindon]

And this is to fill the random numbers in col.A upto the last row Col.D has value...

Sub Sample
Dim x As Long, y As Long
y = Range("d" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Randomize
With CreateObject("Scripting.Dictionary")
    Do
Again:
        x = Int(Rnd * 9000000) + 100000
        If .exists(x) Then GoTo Again
        .add x, Nothing
    Loop While .Count <=y -1
Range("A2").Resize(.Count) = Application.Transpose(.keys)
End With
Application.EnableEvents = True
End Sub

 

[Gary Drumm]

Jindon,
Seems to run great, thanks.

FYI,
I changed the 90000 to 9000, and the 100000 to 1000, and now I have a four digit number. I think that will be easier for me to keep up with.

Thanks again,
Gary

 

[jindon]

Ah... I seem to understand your problem finally...