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.

Function ListPermut(num As Integer)

Dim c As Long, r As Long, p As Long
Dim rng() As Long, temp As Long, i As Long
Dim temp1 As Long, y() As Long, d As Long

p = WorksheetFunction.Permut(num, num)


ReDim rng(1 To p, 1 To num)
For c = 1 To num
  rng(1, c) = c
Next c
For r = 2 To p
  For c = num To 1 Step -1
    If rng(r - 1, c - 1) < rng(r - 1, c) Then
     temp = c - 1
      Exit For
    End If
  Next c
  For c = num To 1 Step -1
    rng(r, c) = rng(r - 1, c)
  Next c
  For c = num To 1 Step -1
      If rng(r - 1, c) > rng(r - 1, temp) Then
         temp1 = rng(r - 1, temp)
          rng(r, temp) = rng(r - 1, c)
          rng(r, c) = temp1
          ReDim y(num - temp)
          i = 0
          For d = temp + 1 To num
            y(i) = rng(r, d)
            i = i + 1
          Next d
          i = 0
          For d = num To temp + 1 Step -1
            rng(r, d) = y(i)
            i = i + 1
          Next d
          Exit For
      End If
  Next c
Next r
ListPermut = rng
End Function

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

Function asPermute(sInp As String) As String()
  ' shg 2015
  ' UDF wrapper for dicPermute

  ' Returns the distinct uppercase permutations of sInp
  ' See combinMississippi.bas for a more robust approach

  Dim dic           As Dictionary
  Dim asOut()       As String
  Dim v             As Variant
  Dim i             As Long

  Set dic = New Dictionary
  dicPermute "", UCase(sInp), dic, 0

  ReDim asOut(1 To dic.Count, 1 To 1)
  For Each v In dic.Keys
    i = i + 1
    asOut(i, 1) = v
  Next v
  asPermute = asOut
End Function

Function dicPermute(sL As String, sR As String, dic As Dictionary, nOut As Long)
  ' Adapted from http://spreadsheetpage.com/index.php/site/tip/generating_permutations/

  Dim i             As Integer
  Dim j             As Integer

  j = Len(sR)
  If j <= 1 Then

    If Not dic.Exists(sL & sR) Then
      nOut = nOut + 1
      dic.Add Key:=sL & sR, Item:=""
      ' Debug.Print nOut
      DoEvents
    End If
  Else
    For i = 1 To j
      dicPermute sL & Mid(sR, i, 1), Left(sR, i - 1) & Right(sR, j - i), dic, nOut
    Next
  End If
End Function

Requires a reference to Microsoft Scripting Runtime.

[daffodil11]

shg code is best code