Permutation problem

[saccino]

Hi - I would like to generate all the 5 digit long permutations that can be constructed from a pool of 30 unique digits.
The pool of 30 digits are: 2, 3, 4, 6, 7, 8, 9, A, B, C, D, E, F, G, H, J, K, L, M, N, P, Q, R, T, U, V, W, X, Y, Z.

[O, 0, 5, S, I, 1] are excluded due to the likelihood of a manual recording error, i.e. confusing O for 0 etc.

An example of a result would be: A27BK

I calculate that there should be 17,100,720 different permutations. Is excel able to handle that size of calculation? (I have excel 2016)

 

[wideboydixon]

This will do it but it's not quick!

Private r As Long
Private c As Long
Public Sub GeneratePermuations()

Const selection = "2346789ABCDEFGHJKLMNPQRTUVWXYZ"
Const depth = 5

ReDim s(1 To Len(selection), 1 To 2)
Dim i As Long

For i = 1 To Len(selection)
    s(i, 1) = Mid$("2346789ABCDEFGHJKLMNPQRTUVWXYZ", i, 1)
    s(i, 2) = 0
Next i

r = 1
c = 1
Application.ScreenUpdating = False
RecursiveGen s, 0, "", depth
Application.ScreenUpdating = True

End Sub
Private Sub RecursiveGen(s, l, p, d)

Dim i As Long

If l = d Then
    Application.StatusBar = p
    Cells(r, c).Value = "'" & p
    r = r + 1
    DoEvents
    Exit Sub
End If

For i = 1 To UBound(s, 1)
    If s(i, 2) = 0 Then
        s(i, 2) = 1
        RecursiveGen s, l + 1, p & s(i, 1), d
        s(i, 2) = 0
    End If
    If l = 0 Then
        c = c + 1
        r = 1
    End If
Next i

End Sub

WBD

 

[gallen]

Well I was bored and thought I'd look at this even though I honestly can't see the point.

This code (if you are patient enough to run it) should do fill cells with all possible permutations.) I tried it to the 2nd permutation of the 2nd character and was at 810000 rows!

WARNING this will take a LONG time, you may need to break it down. Run it at your own risk

Sub CrazyTest()Dim arrChars As Variant
'You may want a progress bar adding to this as it will take a long time as there millions of results.
'character loop integers (0 to 29)
Dim l1 As Integer, l2 As Integer, l3 As Integer, l4 As Integer, l5 As Integer
Dim s As String


Dim lRow As Long, lCol As Long


lRow = 1
lCol = 1
arrChar = Array("2", "3", "4", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "Q", "R", "T", "U", "V", "W", "X", "Y", "Z")
    Application.ScreenUpdating = False
    For l1 = 0 To 29 '1st chr
        s = arrChar(l1)
        For l2 = 0 To 29 '2nd chr
            s = s & arrChar(l2)
            For l3 = 0 To 29 '3rd chr
                s = Left(s, 2) & arrChar(l3)
                For l4 = 0 To 29 '4th chr
                    s = Left(s, 3) & arrChar(l4)
                    For l5 = 0 To 29 '5th chr
                        s = Left(s, 4) & arrChar(l5)
                        Cells(lRow, lCol) = s
                        If lRow = Rows.Count Then
                            'if we are on the last row then move to first row and next column
                            lRow = 1
                            lCol = lCol + 1
                        Else
                            lRow = lRow + 1
                        End If
                    Next l5
                Next l4
            Next l3
        Next l2
    Next l1
    Application.ScreenUpdating = True


End Sub

 

[gallen]

This will do it but it's not quick!


WBD

Can't imagine there IS a quick way.

 

[bobsan42]

imo there should be no problem
just keep in mind its limitations ( ca. 1 M rows x 16 K columns per sheet)

and of course - it will take some time.
do not rely on keeping too much data in the memory so think of a way to save the produced permutations from time to time.
To speed things up a bit maybe use arrays and dump the data on the sheet at once then save. then start a new cycle.

 

[wideboydixon]

This will do it but it's not quick!

Also note you can take out the DoEvents and the Application.StatusBar lines but it will go into not responding mode and you won't see the progress

WBD

 

[wideboydixon]

Well I was bored and thought I'd look at this even though I honestly can't see the point.

This code (if you are patient enough to run it) should do fill cells with all possible permutations.) I tried it to the 2nd permutation of the 2nd character and was at 810000 rows!

Looking at the expected number of permutations, I suspect the OP didn't want any duplicate characters in the output (30 x 29 x 28 x 27 x 26).

WBD