# Combing all variations of cells into long list...



## therealjdj (Jan 4, 2023)

Hey all,
If I have a table like this below and I need to create identifiers which create all possible variations of these. How can you easily do this? 

Have:

YearTypeSubType2022AX2023BY

Need:
2022AX
2022AY
2023AX
2023AY
2022BX
2022BY
2023BX
2023BY

Obviously my real need is for much more data than that. 

Any easy ways?


----------



## Skybluekid (Jan 4, 2023)

What version of excel are you using?


----------



## therealjdj (Jan 4, 2023)

Skybluekid said:


> What version of excel are you using?


Microsoft® Excel® for Microsoft 365 MSO (Version 2209)


----------



## Fluff (Jan 4, 2023)

therealjdj said:


> Microsoft® Excel® for Microsoft 365 MSO (Version 2209)



I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## therealjdj (Jan 4, 2023)

Fluff said:


> I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)Good


Good tip -- this has been done!


----------



## lrobbo314 (Jan 4, 2023)

How about this?

Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022BX42022BY52023AX62023AY72023BX82023BYSheet1


```
Sub Combine()
Dim r As Range:         Set r = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For i = 1 To UBound(AR)
        For xtype = 1 To UBound(AR)
            For stype = 1 To UBound(AR)
                .Add Join(Array(AR(i, 1), AR(xtype, 2), AR(stype, 3)), "")
            Next stype
        Next xtype
    Next i
    r.Offset(-1, 4).Resize(.Count, 1).Value = Application.Transpose(.toArray())
End With
End Sub
```


----------



## therealjdj (Jan 4, 2023)

lrobbo314 said:


> How about this?
> 
> Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022BX42022BY52023AX62023AY72023BX82023BYSheet1
> 
> ...




Magnificent. Thank you!!


----------



## therealjdj (Jan 4, 2023)

lrobbo314 said:


> How about this?
> 
> Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022BX42022BY52023AX62023AY72023BX82023BYSheet1
> 
> ...


Oh one small challenge.... what if they are not the same length? Like column C is longer as it has a 3rd variable?


----------



## lrobbo314 (Jan 4, 2023)

Like this?

Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022AZ4Z2022BX52022BY62022BZ72023AX82023AY92023AZ102023BX112023BY122023BZSheet1


```
Sub Combine()
Dim r As Range:         Set r = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For i = 1 To UBound(AR)
        If AR(i, 1) <> vbNullString Then
            For xtype = 1 To UBound(AR)
                If AR(xtype, 2) <> vbNullString Then
                    For stype = 1 To UBound(AR)
                        .Add Join(Array(AR(i, 1), AR(xtype, 2), AR(stype, 3)), "")
                    Next stype
                End If
            Next xtype
        End If
    Next i
    r.Offset(-1, 4).Resize(.Count, 1).Value = Application.Transpose(.toArray())
End With
End Sub
```


----------



## lrobbo314 (Jan 4, 2023)

Could use dynamic array formula as well.

Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022BZ4Z2022BX52022AY62022AZ72023BX82023BY92023AZ102023AX112023BY122023BZSheet1


```
=LET(
    fb,LAMBDA(x,FILTER(x,x<>"")),
    yr,fb(Table1[Year]),
    tp,fb(Table1[Type]),
    st,fb(Table1[SubType]),
    cnt,PRODUCT(BYCOL(Table1,LAMBDA(c,COUNTA(c)))),
    s,SEQUENCE(cnt,,0),
    a,INDEX(yr,INT(s/(cnt/COUNTA(yr)))+1),
    b,INDEX(tp,(MOD(INT(s/COUNTA(st))+1,2)=0)+1),
    c,INDEX(st,MOD(s,COUNTA(st))+1),
        BYROW(
            HSTACK(a,b,c),
                LAMBDA(r,
                    TEXTJOIN("",,r)
                )
        )
)
```


----------



## therealjdj (Jan 4, 2023)

Hey all,
If I have a table like this below and I need to create identifiers which create all possible variations of these. How can you easily do this? 

Have:

YearTypeSubType2022AX2023BY

Need:
2022AX
2022AY
2023AX
2023AY
2022BX
2022BY
2023BX
2023BY

Obviously my real need is for much more data than that. 

Any easy ways?


----------



## therealjdj (Jan 4, 2023)

lrobbo314 said:


> Could use dynamic array formula as well.
> 
> Combine XYZ.xlsmABCDE1YearTypeSubType2022AX22022AX2022AY32023BY2022BZ4Z2022BX52022AY62022AZ72023BX82023BY92023AZ102023AX112023BY122023BZSheet1
> 
> ...




I am trying to get this version to run, but it's not accepting the Table1[Year],... do I need to define these somehow?


----------



## lrobbo314 (Jan 4, 2023)

Just turn your data into a table by selecting the data and then hit Ctrl+T.

Either that or just substitute Table1[Year] for the data in column A, Table1[Type] for column B, and so on.  Don't include the header information.


----------



## therealjdj (Jan 5, 2023)

lrobbo314 said:


> Just turn your data into a table by selecting the data and then hit Ctrl+T.
> 
> Either that or just substitute Table1[Year] for the data in column A, Table1[Type] for column B, and so on.  Don't include the header information.



Ok - so that is working. However there is a problem:

The data comes out correctly, unless I add additional values into the Type column. It then produces duplicates using the previous values from Type. But it doesn't use the new value(s). 
However when I add additional values to Year or SubType, it works fine....

So if I add a Type C I get this data (duplicates marked in red):

2022AX2022AY2022AZ2022BX2022BY2022BZ2022AX2022AY2022AZ2023BX2023BY2023BZ2023AX2023AY2023AZ2023BX2023BY2023BZ


----------



## lrobbo314 (Jan 5, 2023)

This version seems to be working correctly.


```
=LET(
    fb,LAMBDA(x,FILTER(x,x<>"")),
    tbl,Table1,
    yr,fb(INDEX(tbl,,1)),
    tp,fb(INDEX(tbl,,2)),
    st,fb(INDEX(tbl,,3)),
    cnt,PRODUCT(BYCOL(tbl,LAMBDA(c,COUNTA(c)))),
    s,SEQUENCE(cnt,,0),
    a,INDEX(yr,INT(s/(cnt/COUNTA(yr)))+1),
    b,INDEX(tp,MOD(INT(s/COUNTA(st)),COUNTA(tp))+1),
    c,INDEX(st,MOD(s,COUNTA(st))+1),
           BYROW(
            HSTACK(a,b,c),
                LAMBDA(r,
                    TEXTJOIN("",,r)
                )
        )
)
```


----------



## therealjdj (Jan 5, 2023)

lrobbo314 said:


> This version seems to be working correctly.
> 
> 
> ```
> ...



PERFECT

Thank you Irobbo314! Your support on this the past two days is very much appreciated!


----------



## lrobbo314 (Jan 5, 2023)

Awesome!  Thanks for the feedback.

In case you are interested, I updated the VBA solution as well.  This version is recursive and will accept additional columns as well.  Made it recursive to be more robust and to avoid so many nested for loops.


```
Sub Main()
Dim r As Range:         Set r = Range("Table1")
Dim AR() As Variant:    ReDim AR(1 To r.Columns.Count)
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")

For i = 1 To UBound(AR)
    AR(i) = r.Columns(i).Value
Next i

RX AR, "", 1, AL

Range("F1").Resize(AL.Count).Value = Application.Transpose(AL.toArray)
End Sub

Sub RX(AR As Variant, buffer As String, depth As Integer, AL As Object)
Dim prefix As String

For i = 1 To UBound(AR(depth))
    If Not IsEmpty(AR(depth)(i, 1)) Then
        If buffer = "" Then
            prefix = AR(depth)(i, 1)
        Else
            prefix = Join(Array(buffer, AR(depth)(i, 1)), "")
        End If
        If depth < UBound(AR) Then
            RX AR, prefix, depth + 1, AL
        Else
            AL.Add prefix
        End If
    End If
Next i
End Sub
```


----------

