Forum Discussion

sampie13's avatar
sampie13
Copper Contributor
Nov 29, 2021

Creating combinations of text from a table

Trying to take 4 columns of text and concacinate them into rows with all possible combinations. e.g. 1st combination would be IM-Camp-1_Prospecting_Creative 1_300x250. But I want to create that naming convention for all possible combinations. Not sure what the formula is for this but would be helpful if someone can point it out!

 

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 29, 2021

    sampie13 

    Here is a macro solution:

    Sub CreateCombinations()
    Const c = "K" ' Output column
    Dim r As Long
    Dim r1 As Long, r2 As Long, r3 As Long, r4 As Long
    Dim m1 As Long, m2 As Long, m3 As Long, m4 As Long
    Application.ScreenUpdating = False
    m1 = Range("C1").End(xlDown).Row
    m2 = Range("D1").End(xlDown).Row
    m3 = Range("E1").End(xlDown).Row
    m4 = Range("F1").End(xlDown).Row
    r = 1
    For r1 = 2 To m1
        For r2 = 2 To m2
            For r3 = 2 To m3
                For r4 = 2 To m4
                    r = r + 1
                    Range(c & r).Value = Range("C" & r1).Value & "_" & Range("D" & r2).Value & _
                        "_" & Range("E" & r3).Value & "_" & Range("F" & r4).Value
                Next r4
            Next r3
        Next r2
    Next r1
    Application.ScreenUpdating = True
End Sub

Resources