Permutations in Excel

Question

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!

Answer 1

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