generate combinations of a string and push the others down?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
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
1original combination
22-11-4-29-3
35-5-20-21-25
48-8-20-25-30
51-4-1-21-2
65-11-14-22-3
test


to this

test.xlsm
A
1original combination
22-11-4-29-3
32-3-4-11-29
42-3-4-29-11
52-3-11-29-4
6and etc
7and etc
85-5-20-21-25
98-8-20-25-30
101-4-1-21-2
115-11-14-22-3
test
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi excelNewbie22,

Based on this nifty solution try the following:

VBA Code:
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
 
Upvote 0
Solution
Just noticed that the output was going to Col. B of the active sheet. To fix replace this line...

VBA Code:
Cells(j, 2).Value = items(i)

...with this:

VBA Code:
wsSrc.Cells(j, 1).Value = items(i)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top