Results 1 to 5 of 5

Need a formula to list possible combinations of 5 digit code

This is a discussion on Need a formula to list possible combinations of 5 digit code within the Excel Questions forums, part of the Question Forums category; Hello, I am currently working on a project that i cannot discuss at present, but i am an absolute Excel ...

  1. #1
    New Member
    Join Date
    Aug 2006
    Posts
    2

    Default Need a formula to list possible combinations of 5 digit code

    Hello,

    I am currently working on a project that i cannot discuss at present, but i am an absolute Excel novice and my project requires some help from a pro.

    What I require is a formula to calculate and list every possible combination of numbers that could be in a 5 digit code that contains any numbers between 1 and 50, but without any duplication of numbers within one set of 5 digits. e.g. a bit like lottery numbers or a safe combination.

    I would imagine the total number of possible combinations is somewhere up in the millions, and as such might take quite a while to work out everyone by hand.

    I hope that someone can help me or point me in the right direction of how to solve this problem.

    I apologise for making this so obscure.

    Many thanks

    Wayne

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,185

    Default

    Hi Wayne, welcome to the Board!

    Calculating the number of possible permutations or combinations is straightfoward.
    For permutations: =PERMUT(50,5). The answer is 254,251,200
    For combinations: =COMBIN(50,5). The answer is 2,118,760
    You're after unique combinations -- but where you do want to store over 2 million results, and how would you reference them?

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Aug 2006
    Posts
    2

    Default

    Hi Denis,

    Thanks for the warm welcome and quick reply.

    I was hoping that it would be possible to store the results on either a excel spreadsheet or any other program that would make the results most easily viewable and accessible. Do you have any suggestions on how best to do this and if it is possible to make a code to list the possible combinations?

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,969

    Default Re: Need a formula to list possible combinations of 5 digit

    ... every possible combination of numbers that could be in a 5 digit code that contains any numbers between 1 and 50, but without any duplication of numbers within one set of 5 digits. e.g. a bit like lottery numbers or a safe combination.
    What does the bit about numbers between 1 and 50 mean?

    For example, is
    12345 made up of 1, 2, 3, 4 and 5 different from
    12345 made up of 12, 34 and 5?
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  5. #5
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,185

    Default

    Hi Wayne,

    I'm not sure how you would store and search the possible combinations but I came across a link with an efficient method for generating them --
    http://www.xtremevbtalk.com/showthread.php?t=168296
    Postings 1 and 2 in the thread are discussion / theory, posting 3 is the Combinations code.

    Hope this helps

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com