3

Is it possible to generate a table of permutations in Excel without using VBA and without using any "helper" sheets or rows/columns?

For N columns, there would be N! rows.

For example, the table for N=3 would look like this:

1  2  3
1  3  2
2  1  3
2  3  1
3  1  2
3  2  1
ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51

2 Answers2

10

Open a blank worksheet and paste the following formula into cell A1. (I included line breaks for readability.)

= IF(ROW()<=FACT(COLUMN()-1),COLUMN(),
INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),
IF(COLUMN()=(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+2),1,COLUMN()+1))))

Simply drag this formula over N columns and then down N! rows in order to generate a full permutation table of order N.

A few things to note:

  1. Because factorials grow very quickly, the formula above only works for N<=10. However, this isn't really a limitation because 10! = 3,628,800 which is a number that exceeds the maximum number of rows in Excel 2010 (which is 1,048,576). Therefore, Excel can't generate a permutation table for N=10 anyway.

  2. If you want the top left of the table to be a cell other than A1, the formula can manually be modified to account for this shift. For example, if you want the table to start at A2 instead of A1, replace each instance of ROW() in the formula above with (ROW()-1).

  3. Excluding the first row (which lists the numbers ascending) and the last row (which lists the numbers descending), the order in which the permutations are listed in this table are not in the same order as listed in the original question, but the pattern is still deterministic. See below for a screenshot of a permutation table using this formula of order 4, using conditional formatting to make it easy to spot the pattern of how the order of the permutations change as row numbers increase.

    enter image description here

EDIT:

Another solution with all of the same properties as above except that the permutations are generated in a different order is the following:

= IF(ROW()<=FACT(COLUMN()-1),COLUMN(),
INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),COLUMN()))
+IF(ROW()<=FACT(COLUMN()),-1,
IF(INDIRECT(ADDRESS(ROW()-FACT(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+1),COLUMN()))
=INDIRECT(ADDRESS(ROW(),(SUMPRODUCT(((ROW()-1)>=FACT(ROW($A$2:$A$10)))+0)+2))),1,0)))

This formula is obviously longer than the first formula, but it still generally accomplishes the same thing: Open a blank worksheet and paste this formula into cell A1. Then drag the formula over N columns and then down N! rows in order to generate a full permutation table of order N.

As already stated, however, the order of the permutations changed as compared to the previous solution. The order of permutations in this solution is arguably better than that of the first solution because each column always contains "blocks" of numbers of the same size. See below for a screenshot of a permutation table using this formula of order 4, using conditional formatting to make it easy to spot the pattern of how the order of the permutations change as row numbers increase.

enter image description here

ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51
  • Your first formula bombed out, giving a final row of 987656789. Your second formula seems OK with a final row of 987654321, although I did not personally examine all 9! permutations. But you solved my problem, so thanks! – richard1941 Aug 11 '18 at 21:10
  • I apologize and eat crow! After a half hour coke break I returned to my confuzer, and found that your first formula had the last row correct. I suspect a glitch in excel, as there was no indication that it was still computing. Saving the file may have triggered the recalc. – richard1941 Aug 11 '18 at 21:25
  • It is worse than I suspected: a file save is the ONLY way I can get excel to recalculate. I wonder if open office would do any better... – richard1941 Aug 11 '18 at 21:47
  • This was back when I was kinda a noob to excel, this could be done better with non-volatile formulas. All the formulas here are volatile. – ImaginaryHuman072889 Aug 12 '18 at 02:21
1

For those stumbling upon this in 2021 and beyond: if you have access to Power Query, a Cross join is a straightforward way to achieve this (and technically not VBA and doesn't use "helper" columns)

See documentation here.

ffi
  • 353
  • 4
  • 11