Formula for 12345

[MRozell]

Is there a formula that would give me all the possible combination of 5 digits.
In column A I have the Area A B C D E. Each area has a number in column B. Column D is not needed but it show the possible #. Column E-I shows the Digit position. And J shows the all the next to each other in one number (I have the formula worked out for J)
Is there a way to auto figure all the possible number combinations without me sitting and type every possible combination hoping I got them all and didn't miss any.

[daffodil11]

You can do this quickly with a little bit of VBA.

First, let's calculate the actual number of permutations: =PERMUT(5,5) = 120.

To list them, we use the UDF below.

We select E2:I121 since we know there are 121 permutations to cover. Next we enter =ListPermut(5) and use Ctrl+Shift+Enter to exit the cell and apply the array formula to all cells simultaneously.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

[shg]

UDF?

	A	B
1	12345
2	12345	A2:A121: {=asPermute(A1)}
3	12354
4	12435
5	12453
6	12534
7	12543
8	13245
115	53421
116	54123
117	54132
118	54213
119	54231
120	54312
121	54321

Please Login or Register  to view this content.

Requires a reference to Microsoft Scripting Runtime.

[daffodil11]

shg code is best code