Random number generator

[jcaptchaos2]

Hello,

I am using this to generate a random number that can not be repeated for a rolling 12 months.

=ROUND((RAND()*10000000),0)

I am using this for a label ID number and just received a call that they have duplicate. I am not sure how this works but it seems like a duplicate would be like winning the lottery. Is there a way that I could make it so this number would not repeat for at least 12 months?

 

[xenou]

Hi,
I sort of think a simple solution would be to save the last number to a hidden worksheet and increment it each time one is used.
However, I came up with this solution based on the number of hours passed in the current year, plus minute and second.

I took it as a constraint the the number must be 8 digits, as your is (under 10 million), and a numeric value.

[COLOR="Navy"]Option Explicit[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Declare[/COLOR] [COLOR="Navy"]Function[/COLOR] Sleep [COLOR="Navy"]Lib[/COLOR] "kernel32" (ByVal dwMilliseconds [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] Random_Key() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Static[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
[COLOR="SeaGreen"]'To create a random number based on the date (ignoring year)[/COLOR]

[COLOR="SeaGreen"]'Example: April 25 @ 2:20 PM and 45 seconds[/COLOR]
[COLOR="SeaGreen"]'         result --> "275020451"[/COLOR]
[COLOR="SeaGreen"]'         [2750] hours passed this year...[/COLOR]
[COLOR="SeaGreen"]'         [2045] at the twentieth minute and 45th second...[/COLOR]

[COLOR="SeaGreen"]'Keys will repeat in successive years[/COLOR]
[COLOR="SeaGreen"]'Jan/Feb keys are returned with leading zeros[/COLOR]
[COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]

    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] (Now - d) < (1 / 86400) [COLOR="SeaGreen"]'//wait at least 1 second between function calls[/COLOR]
        Sleep 100
    [COLOR="Navy"]Loop[/COLOR]
    d = Now
    
    a = (Val(Format(d - 1, "y")) * 24) + (Val(Format(d, "hh"))) [COLOR="SeaGreen"]'Num of hours in this year to the present hour[/COLOR]
    b = Format(d, "nnss") [COLOR="SeaGreen"]'Minute and second of this day[/COLOR]
    Random_Key = Format(a, "0000") & b

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Here is a more fully commented version with Test Code:

[COLOR="Navy"]Option Explicit[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Declare[/COLOR] [COLOR="Navy"]Function[/COLOR] Sleep [COLOR="Navy"]Lib[/COLOR] "kernel32" (ByVal dwMilliseconds [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Private[/COLOR] mbln_TEST_PROC [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] Random_Key() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Static[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
[COLOR="SeaGreen"]'To create a random number based on the date (ignoring year)[/COLOR]

[COLOR="SeaGreen"]'Example: April 25 @ 2:20 PM and 45 seconds[/COLOR]
[COLOR="SeaGreen"]'         result --> "275020451"[/COLOR]
[COLOR="SeaGreen"]'         [2750] hours passed this year...[/COLOR]
[COLOR="SeaGreen"]'         [2045] at the twentieth minute and 45th second...[/COLOR]

[COLOR="SeaGreen"]'Keys will repeat in successive years[/COLOR]

[COLOR="SeaGreen"]'Notes:[/COLOR]
[COLOR="SeaGreen"]'    * Leading zeros in Jan/Feb could be stripped off if value is used as a number (numbers will still be unique)[/COLOR]
[COLOR="SeaGreen"]'    * Add a year prefix to maintain uniqueness across years:[/COLOR]
[COLOR="SeaGreen"]'        Rand_Key = Format(d,"yyyy") & format(a,"0000") & b[/COLOR]
[COLOR="SeaGreen"]'    * Add a static incrementer prefix/postfix to maintain uniqueness across faster successive calls (without using Sleep)[/COLOR]
[COLOR="SeaGreen"]'        Rand_Key = Format(d,"yyyy") & format(a,"0000") & b & myCounter[/COLOR]

[COLOR="SeaGreen"]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]

    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] (Now - d) < (1 / 86400) [COLOR="SeaGreen"]'//wait at least 1 second between function calls[/COLOR]
        Sleep 100
    [COLOR="Navy"]Loop[/COLOR]
    d = Now
    
    [COLOR="SeaGreen"]'TEST CODE ------------[/COLOR]
    [COLOR="Navy"]If[/COLOR] mbln_TEST_PROC [COLOR="Navy"]Then[/COLOR]
        d = Get_Test_Date()
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="SeaGreen"]'----------------------[/COLOR]
    
    a = (Val(Format(d - 1, "y")) * 24) + (Val(Format(d, "hh"))) [COLOR="SeaGreen"]'Num of hours in this year to the present hour[/COLOR]
    b = Format(d, "nnss") [COLOR="SeaGreen"]'Minute and second of this day[/COLOR]
    Random_Key = Format(a, "0000") & b
    
    [COLOR="SeaGreen"]'TEST CODE ------------[/COLOR]
    [COLOR="Navy"]If[/COLOR] mbln_TEST_PROC [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Format(d, "yyyy-mm-dd hh:nn:ss") & " Random Key: " & Random_Key
        d = 0
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="SeaGreen"]'----------------------[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Test_It()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    mbln_TEST_PROC = False
    Randomize
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 50
        [COLOR="Navy"]Call[/COLOR] Random_Key
    [COLOR="Navy"]Next[/COLOR] i

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Function[/COLOR] Get_Test_Date() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Static[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]

[COLOR="Navy"]If[/COLOR] Year(d) > Year(Date) [COLOR="Navy"]Or[/COLOR] d = 0 [COLOR="Navy"]Then[/COLOR]
    d = DateSerial(Year(Date), 1, 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
d = d + (Rnd() * (360 / 15))
Get_Test_Date = d

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[jcaptchaos2]

Thank you for your help, as it sits now I am using this number on labels that we print out and the numbers labels have to be in succession, I have the first label read from cell "H1" and then just have a formula in H2 (H1+1) and I copy that down to H50. We print 50 labels at a time. The person then enters an order number which changed the Random number in H1 but somehow it repeated in a two week span. Where do I put the code that you have supplied so it enters the random number into cell "H1"?

 

[xenou]

I would probably have a command button, double-click event, or custom button - something like that. Or just a macro that you run. The code would simply be:

Sub Foo()
Range("H1").Value = CLng(Random_Key)
End Sub

The code should be made a public function rather than a private one, and put in a standard module.

 

[xenou]

mbln_TEST_PROC = False

This should have been true in my Test_It procedure (if you try it).

 

[jcaptchaos2]

I tried what you suggested but all I got in cell H1 is 0

 

[jcaptchaos2]

I do like your idea about using the date and time which will necer repeat do if I could just use the = now() formula on "H1"
and then get "H2" to be one more than "H1" and so on down to "H50" that would be perfect.
I tried to format "H1" as ddhhmmss so "H1" looks good 8 digets but in "H2" I need a formula like "H1-1" but the *1 is not correct as I would need it to be 1 I think second less so if in "H1" the number was 78965412 I would need "H2" to be 78965411

 

[xenou]

Hi,

Using time value ddhhnnss would still fail to guarantee uniqueness (these values repeat once each day every day).

I really think if you are able to assign the numbers from the same workbook all the time, just store the last number you used.

For example, I've created a sheet called "DAT" to hold the last number used (and hide the sheet). Then I increment by 50 every time I need a new number (which I'll put in Sheet1 Range A1.

<a href="http://northernocean.net/etc/mrexcel/20110425_book1.zip">SAMPLE WORKBOOK (zip file format)</a>

Code:

Sub Next_Num()
Dim i As Long
Dim j As Long
Dim a(1 To 50, 1 To 1) As Long

    i = Worksheets("DAT").Range("A1").Value + 0
   
    For j = 1 To 50
        i = i + 1
        a(j, 1) = i
    Next j
    
    Worksheets("DAT").Range("A1").Value = i
    
    Worksheets("Sheet1").Range("A1").Resize(50, 1).Value = a
    ThisWorkbook.Save

End Sub

Here, by the way, would be an example of the original number generator from my first post:
<a href="http://northernocean.net/etc/mrexcel/20110425_book2.zip">SAMPLE WORKBOOK (zip file format)</a>
(though since you take batches of 50 there is a 50 second interval in which duplicates could still be served up ... )

 

[jcaptchaos2]

Ok, very cool, I have a print macro button that I could tie this to as the have to press it to print the labels. Thanks so much,