+ Reply to Thread
Results 1 to 10 of 10

Permutation & Combination

  1. 09-29-2015, 08:07 AM #1
    Jiaying
    Jiaying is offline
    Registered User
    Join Date
    09-29-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Permutation & Combination

    Hi,

    Recently I was trying to work on Permutation Combination keys in excel. The problem I was facing was I had a set of 26 characters and I wanted to know how many combinations of 3 letters can be generated and what are they. I tried using PERMUT, CONCATINATE, etc functions but failed. can someone please help me in this.

    Problem Example:

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z.

    AAB, AAC, BAD, CAB, EEE.........????

    AAB is the same as ABA.

    Is there any formula that can generate all these options for me?

    Regards,
    Jiaying
    Reply With QuoteRegister To Reply
  2. 09-29-2015, 08:17 AM #2
    LJMetzger
    LJMetzger is offline
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Permutation & Combination

    Hi Jiaying and welcome to ExcelForum,

    The following tutorial should help you with your formulas: http://www.stat.wisc.edu/~ifischer/I...and_Combos.pdf

    I also like to use the following table: http://www.rpbridge.net/7z78.htm

    Lewis
    Reply With QuoteRegister To Reply
  3. 10-01-2015, 02:48 AM #3
    Jiaying
    Jiaying is offline
    Registered User
    Join Date
    09-29-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Permutation & Combination

    Thanks. I know how to calculate the number of permutation. What i need is for excel to help me generate out all the possible combinations.
    Reply With QuoteRegister To Reply
  4. 10-01-2015, 02:54 AM #4
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Permutation & Combination

    Isn't there a COMBIN function in XL?

    Anyway, the number of combinations of nu numbers m by m is n!/(m!*(n-m)!)
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  5. 10-01-2015, 04:01 PM #5
    Jiaying
    Jiaying is offline
    Registered User
    Join Date
    09-29-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Permutation & Combination

    I mean i want it to generate out every single combination.

    AAA
    AAB
    AAC
    AAD
    AAE
    AAF
    AAG
    AAH
    AAI
    AAJ
    AAK
    AAL
    AAM
    .
    .
    .
    Reply With QuoteRegister To Reply
  6. 10-01-2015, 04:30 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
    2003, 2010
    Posts
    40,678

    Re: Permutation & Combination

    See the workbook at https://app.box.com/s/47b28f19d794b25511be

    There are 17,576.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 10-01-2015, 04:38 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Permutation & Combination

    Or you could use a UDF:

    Please Login or Register  to view this content.
    Row\Col
    A
    B
    1
    		 AAA A1: =DecToBij(ROW() + 702, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
    2
    		 AAB
    3
    		 AAC
    4
    		 AAD
    5
    		 AAE
    6
    		 AAF
    7
    		 AAG
    8
    		 AAH
    9
    		 AAI
    10
    		 AAJ
    11
    		 AAK
    12
    		 AAL
    13
    		 AAM
    14
    		 AAN
    15
    		 AAO
    16
    		 AAP
    17
    		 AAQ
    18
    		 AAR
    19
    		 AAS
    20
    		 AAT
    21
    		 AAU
    22
    		 AAV
    23
    		 AAW
    24
    		 AAX
    25
    		 AAY
    26
    		 AAZ
    27
    		 ABA
    Reply With QuoteRegister To Reply
  8. 10-02-2015, 01:58 PM #8
    Jiaying
    Jiaying is offline
    Registered User
    Join Date
    09-29-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Permutation & Combination

    Thanks for the code. But i need it such tat ABA does not appear, as it is the same as AAB.
    Reply With QuoteRegister To Reply
  9. 10-02-2015, 06:11 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Permutation & Combination

    You mean this?

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    1
    		 AAA BBB CCC DDD EEE FFF GGG HHH III JJJ KKK LLL MMM NNN OOO PPP QQQ RRR SSS TTT UUU VVV WWW XXX YYY ZZZ
    2
    		 AAB BBC CCD DDE EEF FFG GGH HHI IIJ JJK KKL LLM MMN NNO OOP PPQ QQR RRS SST TTU UUV VVW WWX XXY YYZ
    3
    		 AAC BBD CCE DDF EEG FFH GGI HHJ IIK JJL KKM LLN MMO NNP OOQ PPR QQS RRT SSU TTV UUW VVX WWY XXZ YZZ
    4
    		 AAD BBE CCF DDG EEH FFI GGJ HHK IIL JJM KKN LLO MMP NNQ OOR PPS QQT RRU SSV TTW UUX VVY WWZ XYY
    5
    		 AAE BBF CCG DDH EEI FFJ GGK HHL IIM JJN KKO LLP MMQ NNR OOS PPT QQU RRV SSW TTX UUY VVZ WXX XYZ
    6
    		 AAF BBG CCH DDI EEJ FFK GGL HHM IIN JJO KKP LLQ MMR NNS OOT PPU QQV RRW SSX TTY UUZ VWW WXY XZZ
    7
    		 AAG BBH CCI DDJ EEK FFL GGM HHN IIO JJP KKQ LLR MMS NNT OOU PPV QQW RRX SSY TTZ UVV VWX WXZ
    8
    		 AAH BBI CCJ DDK EEL FFM GGN HHO IIP JJQ KKR LLS MMT NNU OOV PPW QQX RRY SSZ TUU UVW VWY WYY
    9
    		 AAI BBJ CCK DDL EEM FFN GGO HHP IIQ JJR KKS LLT MMU NNV OOW PPX QQY RRZ STT TUV UVX VWZ WYZ
    10
    		 AAJ BBK CCL DDM EEN FFO GGP HHQ IIR JJS KKT LLU MMV NNW OOX PPY QQZ RSS STU TUW UVY VXX WZZ
    11
    		 … … … … … … … … … … … … … … … … … … … … … …
    Reply With QuoteRegister To Reply
  10. 12-03-2015, 04:55 AM #10
    Jiaying
    Jiaying is offline
    Registered User
    Join Date
    09-29-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    5

    Re: Permutation & Combination

    Yup thankyou! Do you have the code?
    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. Permutation Combination
    By nilesh.parekh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 09:24 AM
  2. Permutation and Combination
    By DEWAL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 08:31 AM
  3. Combination and permutation
    By stanlori in forum Excel General
    Replies: 0
    Last Post: 05-15-2011, 09:10 PM
  4. Permutation Combination
    By Amarjeet Singh in forum Excel General
    Replies: 3
    Last Post: 03-18-2011, 04:43 AM
  5. Macro for permutation and combination
    By anjanjb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2010, 06:22 PM
  6. Permutation/Combination Question...Maybe
    By BLEED in forum Excel General
    Replies: 1
    Last Post: 11-19-2009, 09:42 PM
  7. 5 Digit Combination/Permutation??
    By TJD in forum Excel General
    Replies: 4
    Last Post: 07-07-2008, 01:07 PM
  8. Permutation and combination
    By vishu in forum Excel General
    Replies: 2
    Last Post: 05-19-2005, 03:06 AM

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