+ Reply to Thread
Results 1 to 2 of 2

Auto-Generate random set of four numbers without duplicates?

  1. #1
    Registered User
    Join Date
    07-10-2011
    Location
    USA
    MS-Off Ver
    Office 2008 for Mac
    Posts
    1

    Auto-Generate random set of four numbers without duplicates?

    Hello,

    I'm still learning Excel and I would like help on how to automatically generate four digit numbers without repeating the same number in any four number combination.

    Eg. The list would include: 1234, 4321, but not include 1123, 2244.

    There must be an easy way to do this in Excel.

    Could anyone help me please?

    Thank you very much!

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Auto-Generate random set of four numbers without duplicates?

    Welcome to the forum.

    In A1 type Number
    In B1 type Duplicate Digit

    In A2 type this formula:
    Please Login or Register  to view this content.
    In B2 type this formula:
    Please Login or Register  to view this content.
    Fill both formulas down to row 10001.


    You now have a list of every possible 4 digit number from 0000 to 9999 but these numbers contain duplicate digits.

    In D1 type Duplicate Digit
    In D2 type FALSE
    In G1 type Number

    Select A1:B10001
    Go to the Data tab and click on "Advanced"
    An advanced filter window will appear. Fill it in as follows:

    Action = Copy to another location
    List range = A1:B10001
    Criteria range = D1:D2
    Copy To = G1

    Click on OK. You now have your list of 4 digit numbers which do not contain duplicate digits. The list should go from G1:G5041 (including the header). You do not need to repeat this process again.

    Now, you can randomly pick one of those numbers using the RANDBETWEEN() function.
    In J1 type in Random Number
    In J2 type in this formula:
    Please Login or Register  to view this content.
    This formula will return a random number from your list. Each time you press F9 it will randomly pick a number from the list.
    Attached Files Attached Files
    Last edited by Colin Legg; 07-10-2011 at 06:09 PM.
    Hope that helps,

    Colin

    RAD Excel Blog

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1