+ Reply to Thread
Results 1 to 2 of 2

Generating permutations (and maybe combinations) without VBA

  1. #1
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,366

    Generating permutations (and maybe combinations) without VBA

    This has been rattling around in my head for a while. Some form of "generate all combinations/permutations of something" seems a common request/problem presented around here. Much of the time, the strategy recommended for generating these is some form of nested For..Next loops in VBA. Well, I decided to see if I could do it directly in the spreadsheet without VBA, (though I did use the basic idea of a For i=...Next i loop as my inspiration).

    I chose the "all permuations of three numbers where the numbers can be 1 through 5" as my illustration. In VBA (and other programming languages), one might start with:
    Please Login or Register  to view this content.
    Of course, what the next statement does is look at the current value of ix. If ix is greater than or equal to 5, then it ends that loop. If not, it increments ix and goes back to the matching For statement.

    In the first column, this is pretty easy. =IF(B5>=B$2,B$1,B5+1)
    In subsequent columns, the test is more complex, but still relatively straightforward. =IF(AND(A5>=A$2,A6<>A5),IF(B5>=B$2,B$1,B5+1),B5)
    Copy those formulas down as far as needed to get all permutations. Copy across to get more columns.

    This example is only for numbers, but it can apply to other data types by simply storing the desired text or other values in an array, and accessing the elements using an INDEX() function. (I channeled my inner paleontologist for this one).

    Of course, this is just a start for the kinds of problems that can be addressed. The strategy could be adapted to permutations without duplication or combinations where order does not matter. For now, this is a start to showing that this can be done without VBA.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  2. #2
    Forum Contributor
    Join Date
    11-29-2014
    Location
    brasil
    MS-Off Ver
    excel 2014
    Posts
    1,761

    Re: Generating permutations (and maybe combinations) without VBA

    ex=a a a b b b
    a a a b b c
    a a a b b d
    a a a b b e
    a a a b b f
    a a a b c c
    a a a b c d
    a a a b c e
    a a a b c f
    a a a b d d
    a a a b d e
    a a a b d f
    a a a b e e
    a a a b e f
    ..........
    ........
    .......
    eeefff

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Permutations and combinations
    By WELLO in forum Excel General
    Replies: 0
    Last Post: 11-28-2012, 11:59 AM
  2. Combinations and Permutations
    By curious1201 in forum Excel General
    Replies: 0
    Last Post: 04-19-2012, 05:21 PM
  3. combinations/permutations
    By KBownds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-23-2012, 11:18 AM
  4. Generating Permutations and Combinations
    By e4excel in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-25-2011, 03:17 PM
  5. Combinations/Permutations???
    By seneca in forum Excel General
    Replies: 2
    Last Post: 02-16-2010, 01:49 PM
  6. All permutations and combinations
    By mudkum in forum Excel General
    Replies: 3
    Last Post: 04-24-2009, 04:17 PM
  7. Excel 2007 : Combinations/permutations
    By kdol in forum Excel General
    Replies: 2
    Last Post: 01-03-2009, 02:18 AM
  8. Combinations, Permutations
    By scantor145 in forum Excel General
    Replies: 1
    Last Post: 11-29-2005, 09:30 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1