generate combinations of a string and push the others down?

[excelNewbie22]

hi!
how can I generate all combinations (120) of string of 5 numbers separated by hyphen in a2 but only below it,
and also push the next string in a3 (and a4 and a5 and etc) to cell a121
and again, generate all combinations of a121 below it and push the next strings in a122 (and a123 and etc) to a240 and so on...

test.xlsm
	A
1	original combination
2	2-11-4-29-3
3	5-5-20-21-25
4	8-8-20-25-30
5	1-4-1-21-2
6	5-11-14-22-3
test

to this

test.xlsm
	A
1	original combination
2	2-11-4-29-3
3	2-3-4-11-29
4	2-3-4-29-11
5	2-3-11-29-4
6	and etc
7	and etc
8	5-5-20-21-25
9	8-8-20-25-30
10	1-4-1-21-2
11	5-11-14-22-3
test

 

[Trebor76]

Hi excelNewbie22,

Based on this nifty solution try the following:

Option Explicit
'Source: https://stackoverflow.com/questions/47391728/generating-permutations-of-multiple-cells-in-a-database-based-on-input
Sub ListPermutations()

    Dim i As Long, j As Long, k As Long, n As Long
    Dim items As Variant
    Dim strOriginalList() As String
    Dim wsSrc As Worksheet
    Dim rngCell As Range
    
    Set wsSrc = ThisWorkbook.Sheets("test") 'Sheet name containing the data. Change if required.
    j = wsSrc.Cells(Rows.Count, "A").End(xlUp).Row 'Assumes original list is in Col. A. Change if required.
    
    For Each rngCell In wsSrc.Range("A2:A" & j)
        ReDim Preserve strOriginalList(k)
        strOriginalList(k) = rngCell.Value
        k = k + 1
    Next rngCell
    
    wsSrc.Range("A2:A" & j).ClearContents
    j = 2
    
    For k = LBound(strOriginalList) To UBound(strOriginalList)
        n = UBound(Split(strOriginalList(k), "-")) + 1
        ReDim items(1 To n)
        For i = 1 To n
            items(i) = Split(strOriginalList(k), "-")(i - 1)
        Next i
        items = Permutations(items, UBound(Split(strOriginalList(k), "-")) + 1)
        For i = 1 To UBound(items)
            Cells(j, 2).Value = items(i)
            j = j + 1
        Next i
    Next k
    
End Sub
Function Permutations(items As Variant, r As Long, Optional delim As String = "-") As Variant
    'items is a 1-based array of items
    'returns all nPr permutations of items
    'returns a 1-based variant array
    'where each item is a delimited string
    'represented the permutation
    'r is assumed to be < n

    Dim n As Long, i As Long, j As Long, k As Long
    Dim rest As Variant, perms As Variant
    Dim item As Variant

    n = UBound(items) 'number of items
    ReDim perms(1 To Application.WorksheetFunction.Permut(n, r))

    If r = 1 Then
        'basis case
        For i = 1 To n
            perms(i) = items(i)
        Next i
    Else
        k = 1
        For i = 1 To n
            item = items(i)
            ReDim rest(1 To n - 1)
            For j = 1 To n - 1
                If j < i Then
                    rest(j) = items(j)
                Else
                    rest(j) = items(j + 1)
                End If
            Next j
            rest = Permutations(rest, r - 1)
            For j = 1 To UBound(rest)
                perms(k) = item & delim & rest(j)
                k = k + 1
            Next j
        Next i
    End If
    
    Permutations = perms
    
End Function

Note as I'm not the sole author of the above code I can't offer too much support for it I'm afraid. I did write some of the code for the ListPermutations macro so I can offer some advice there if needed.

Regards,

Robert

 

[Trebor76]

Just noticed that the output was going to Col. B of the active sheet. To fix replace this line...

Cells(j, 2).Value = items(i)

...with this:

wsSrc.Cells(j, 1).Value = items(i)

 

[excelNewbie22]

p-e-r-f-e-c-t !!!!!!!!!
thanks a lot!!!

 

[Trebor76]

You're welcome