Permutations in Excel

I am have a string with 6 spaces,开发者_如何学运维 e.g. 000000. Each space can hold one of three digits - 0, 1, or 2. I know that I can get a total of 120 permutations using the Permut function in Excel, i.e. =PERMUT(6,3) = 120. But I would actually like to have each individual permutation in a cell, e.g. 000001, 000010, etc.. Ideally, the end result would be 120 rows of unique 6-digit IDs.

Please help if you know a faster way of accomplishing this without entering the figures manually.

Thanks!

There is a VBA functionin the last post on this page. Copy it into a VBA module, then in Excel, create a column of integers from 0 to n where n = the number of IDs you want. In the next column, call the VBA function with the value from the first column as the first argument, and 3 as the second argument. Something like

Column A     Column b
0            =baseconv(A1, 3)
1            =baseconv(A2, 3)
2            =baseconv(A3, 3)
...          etc.

Your IDs are really just incremental values using a base 3 counting system. You can format the output to get leading zeros with a custom format of '000000'.

Incidentally, with 6 positions and 3 available values, you can get 3 ^ 6, or 729 unique IDs

First, I don't think you're using PERMUT correctly here. What PERMUT(6,3) gives you is the total number of ways to arrange three things picked out of a set of six things. So the result is 120 because you could have 6*5*4 possible permutations. In your case you have 3^6 = 729 possible strings, because each position has one of three possible characters.

Others have posted perfectly fine VBA-based solutions, but this isn't that hard to do in the worksheet. Here is an array formula that will return an array of the last six digits of the ternary (base-3) representation of a number:

=FLOOR(MOD(<the number>,3^({5,4,3,2,1,0}+1))/(3^{5,4,3,2,1,0}),1)

(As WarrenG points out, just getting a bunch of base-3 numbers is one way to solve your problem.)

You would drag out the numbers 0 through 728 in a column somewhere, say $A$1:$A$729. Then in $B$1:$G$1, put the formula:

=FLOOR(MOD(A1,3^({5,4,3,2,1,0}+1))/(3^{5,4,3,2,1,0}),1)

remembering to enter it as an array formula with Ctrl-Shift-Enter. Then drag that down through $B$729:$G$729.

Finally in cell $H$1, put the formula:

=CONCATENATE(B1,C1,D1,E1,F1,G1)

and drag that down through $H$729. You're done!