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.