+ Reply to Thread
Results 1 to 15 of 15

How to generate 750,000 unique random alpha numeric numbers without duplicates?

  1. 09-09-2016, 02:07 AM #1
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Hi,

    I want to generate total 750,000 unique random combination of alpha and numeric numbers without duplicates, without using "O", "0", "1", "i".
    The number of digits is 16.

    Is there anyone here can teach me how to do it?



    Thanks!
    Reply With QuoteRegister To Reply
  2. 09-09-2016, 02:55 AM #2
    Neil_
    Neil_ is offline
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Run the FillSheet() sub

    Please Login or Register  to view this content.
    EDIT: Previous code had unbroken loop problem.
    Last edited by Neil_; 09-09-2016 at 03:01 AM.
    Frob first, tweak later
    Reply With QuoteRegister To Reply
  3. 09-09-2016, 03:50 AM #3
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,180

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Or try ...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 09-09-2016, 12:19 PM #4
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Thanks Phuocam!
    You are great!
    I have 2 questions:
    1. If i want to remove "l" as well, i just simply take off "l" from
    Temp = "23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjklmnpqrstuvwxyz"??

    2. If i want to remain 16 digit, but do not want to include small letter alphabetic, how should the code revise?
    I tried to remove from Temp = "23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjklmnpqrstuvwxyz", but some of the random generated combination is not in 16 digits.

    Please kindly advise.
    Last edited by jeffreybrown; 09-09-2016 at 08:13 PM. Reason: Removed full quote. Causes clutter.
    Reply With QuoteRegister To Reply
  5. 09-09-2016, 12:22 PM #5
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Thanks Neil!
    You are great!
    I have 2 questions:
    1. If i want to remove "l" as well, how should i do?

    2. If i want to remain 16 digit, but do not want to include small letter alphabetic, how should the code revise?

    Please kindly advise.
    Last edited by jeffreybrown; 09-09-2016 at 08:11 PM. Reason: Removed full quote. Causes clutter
    Reply With QuoteRegister To Reply
  6. 09-09-2016, 03:00 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Another way:

    Please Login or Register  to view this content.
    A
    1
    		 YTQXXASH6NJ328GP
    2
    		 34UTK4WE4MQY2JK7
    3
    		 QEKA7ML87MV5V9QG
    4
    		 QCFT229FT3CGBAK7
    5
    		 4QQTSFRC48TC26SD
    6
    		 LKBSG7P2C2JCRKFG
    7
    		 W5TYJFV389CVSR9D
    8
    		 NYT66MYJLC88WWCR
    9
    		 KSFXFF6V4GD37967
    10
    		 U83ZTSABEEMCE5F3
    11
    		 …
    Last edited by shg; 09-09-2016 at 03:04 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 09-09-2016, 08:08 PM #7
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,180

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Quote Originally Posted by wulikaka View Post
    1. If i want to remove "l" as well, i just simply take off "l" from
    Temp = "23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjklmnpqrstuvwxyz"??
    Yes,

    change:

    K = Int(Rnd() * 56) + 1

    To:

    K = Int(Rnd() * 55) + 1

    55 = Len("23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghjkmnpqrstuvwxyz")
    Reply With QuoteRegister To Reply
  8. 09-09-2016, 10:32 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    A disadvantage of using Rnd in that fashion is that given the first result, you can predict the rest of the sequence.
    Reply With QuoteRegister To Reply
  9. 09-09-2016, 10:50 PM #9
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Thanks shg!!!
    If I want to reduce the number of digits from 16 to 8, how should I edit?
    Reply With QuoteRegister To Reply
  10. 09-09-2016, 10:57 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates? 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 09-11-2016, 06:30 AM #11
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Thanks shg!!
    You are so great.
    Now it is 8 digits random combination.

    Actually my goal is to make it become alphanumeric, I realise when i generate the code out, some are not alphanumeric.

    Is it possible to make it at least 1 number at random position among the 8 digits?
    Reply With QuoteRegister To Reply
  12. 09-11-2016, 01:33 PM #12
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Is this the last iteration?
    Reply With QuoteRegister To Reply
  13. 09-11-2016, 02:42 PM #13
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates? 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 09-11-2016, 02:44 PM #14
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    The result could still be interpreted by Excel as a number, e.g., 12345E67
    Reply With QuoteRegister To Reply
  15. 09-21-2016, 05:55 AM #15
    wulikaka
    wulikaka is offline
    Registered User
    Join Date
    09-09-2016
    Location
    malaysia
    MS-Off Ver
    2010
    Posts
    8

    Re: How to generate 750,000 unique random alpha numeric numbers without duplicates?

    Hi SHG,

    Thanks for your help!
    Yar, the result still be interpreted by excel as number, eg:12345E67.

    Is it possible to make it
    Total: 6 digits
    requirement: 3 number 3 alphabet
    Random position and combination.

    Is this possible?
    Last edited by wulikaka; 09-22-2016 at 07:07 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to generate 16 digit unique random alpha numeric numbers?
    By matevijay in forum Excel General
    Replies: 22
    Last Post: 06-27-2024, 10:35 AM
  2. Need to generate random numbers with no duplicates
    By mustbe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2015, 05:32 PM
  3. [SOLVED] how to generate random numbers in range 1:20 with unique results
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-23-2014, 08:54 AM
  4. Formula to generate unique random numbers?!?
    By aims in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2013, 03:14 PM
  5. Auto-Generate random set of four numbers without duplicates?
    By adrien23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2011, 06:03 PM
  6. [SOLVED] How to generate sets of random numbers without having duplicates
    By William in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-06-2006, 12:35 AM
  7. [SOLVED] generate unique random numbers
    By Stephen Larivee in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 03-28-2006, 08:10 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1