Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    4 digit PIN creation (97-->)

    I have been requested to make a 4 digit PIN generator. The requirements are for the characters a-z (lower case) and 0-9 to be included in the PIN set. This gives me 1679616 (36^4) combinations. This is within limits of the requirement.

    Now here is my conundrum:

    0-9 is char(48)-char(57) inclusive, and
    a-z is char(97)-char(122) inclusive

    Now, here are my initial thoughts. I was thinking of approaching this as having 4 seperate characters and then concatenating them as a string of 4.
    How can I loop through looking for a random number for 48-57 and 97-122 to assign the character. Am I going about this the wrong way and if so what are your suggestions?
    Jerry

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    In the Analysis toolpak, there is a RandBetween function that generates a number between to values that you can specify!

    Once the random numbers are generated, you can concatenate them, and use data validation on the range of results to check that no duplicate has been made! The formula you can use in the validation is =COUNTIF($A$1:$A$100,A1)=1 where A100 is the last cell reference of the list of PINS.
    Regards,
    Rudi

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 4 digit PIN creation (97-->)

    How about a simple formula like:
    =MID("0123456789abcdefghijklmnopqrstuvwxyz",INT(RA ND()*36+1),1)&MID("0123456789abcdefghijklmnopqrstu vwxyz",INT(RAND()*36+1),1)&MID("0123456789abcdefgh ijklmnopqrstuvwxyz",INT(RAND()*36+1),1)&MID("01234 56789abcdefghijklmnopqrstuvwxyz",INT(RAND()*36+1), 1)

    You could store the string: "0123456789abcdefghijklmnopqrstuvwxyz" in a name or cell if desired and use RANDBetween (if you know the analysis toolpack is installed) instead of the rand function I used.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    Thanks Rudi

    That is useful but I'm not sure it is quite what I want (I think), randbetween(1,100) will give me a random number between 1 and 100 but I want a random number between 48-57 or 97-122, I tried the a range =randbetween(I1:I10,K1:K26) and got a #VALUE! error.

    Any ideas what I am doing wrong?
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    Cor, Now we're cooking with gas, thank the lord for cut and paste, it must have taken you 19 minutes just to type it <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I think I will store the string in a name, good idea. What I like is you have made the answer so simple by using the MID function. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    How about something like this?
    Regards,
    Rudi

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    Thanks Rudi

    That is good but it reduces the permutations by a factor of approximately 31. It is still usable as I have to generate potentially 36000 PINs. Thanks for your help, it will teach me to be more aware of the functions in my Analysis ToolPak <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 4 digit PIN creation (97-->)

    I created the string of the letters (just typed them all out) and the rand function. I then created the mid function using it. Once 1 was created, copy and paste 3 times to make 4 and add the 3 ampersands...

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 4 digit PIN creation (97-->)

    Aahhaa,

    let us raise a <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> to the programmer who first wrote the cut and paste code, <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It wasn't you was it?

    Thanks Steve, works a dream
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •