Multiple Level Combining Into One Column

[jeremypyle]

I need a formula that does the following:

Result Colours Fruit Animals Numbers
blue,apple,dog,6 blue apple dog 6
blue,apple,dog,7 red peach cat 7
blue,apple,dog,8 yellow grape cow 8
blue,apple,dog,9 green 9
blue,apple,cat,6
blue,apple,cat,7


The Result Column is in column A. This will be the column that has formula that I drag down. I believe that should be 144 rows in total from the formula (4 times 3 times 3 times 4). The column names could be anything. And the number of columns is not fixed. There could be more or less columns. Also the number of rows in each column is not fixed.

I can't seem to figure out this formula

excel2.png

[Limor_OP]

deleted by user----

[jeremypyle]

this doesn't work. If I drag down this formula, it just gives the following in every cell

apple.dog.6.

[JohnTopley]

Are you really on Excel 2003? Please update your profile as answers depend on Excel version you have.

[jeremypyle]

sorry it is Microsoft 365 Version 2112 64bit. I didn't realize Excel 2003 was shown in my profile. I've just updated now. Thanks for letting me know

[John Vergara]

Hi jeremypyle!

I leave two options, depends on your Microsoft 365 version.

Op1: Generates a dynamic matrix with all the combinations separated into multiple columns. Then you can join it with TEXTJOIN drag it the formulation.
Op2: All in one dynamic matrix, using BYROW (only work with Microsoft 365 insider version - today -)

Check file. Blessings!

[jeremypyle]

I'm wondering whether perhaps a macro would have worked better for this problem. Do you know if there is a macro somewhere on excelforum that I could use for this? I'm not sure what to search

[JohnTopley]

Sub MLC()
Dim lr, nr, ns, cr
Dim t() As String
Dim lc As Long, nrows As Long, j As Long, n As Long, i As Long
Dim ws1 As Worksheet
'*****************************************************************************************************
'***                                                                                               ***
'***   REULTS are in Column A                                                                      ***
'***   INPUT is Column B and to the right                                                          ***
'***                                                                                               ***
'*****************************************************************************************************

Set ws1 = Worksheets("Sheet1")
ws1.Activate

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

lc = Cells(1, Columns.Count).End(xlToLeft).Column - 1 ' Get last column and hence number of columns
'lc = 4
ReDim lr(1 To lc) ' number of rows per column
ReDim cr(1 To lc) ' MOD parameters

cr(lc) = 1  'set value to intial cumulative column parameters

For i = lc To 1 Step -1
    lr(i) = Cells(Rows.Count, i + 1).End(xlUp).Row - 1          ' number of rows per column
    If i = lc Then cr(i) = lr(i) Else cr(i) = cr(i + 1) * lr(i) ' MOD parameters for each coumn
Next i

nrows = cr(1) ' Total of rows to be calculated

For j = 1 To nrows ' Loop through rows to be calculated
 
    xstr = ""
    For n = lc To 1 Step -1  ' Loop through columns (last to first)
       If n = lc Then
           ' =MOD((ROWS($1:1)-1),$K$2)+1
           idx = ((j - 1) Mod cr(n)) + 1
       Else
           ' =INT(((MOD((ROWS($1:1)-1),$J$2)+1)-1)/$K$2)+1
           idx = Int(((((j - 1) Mod cr(n) + 1) - 1) / cr(n + 1))) + 1
       End If
       xstr = xstr & idx 'Create row indices
    Next n
    
    result = ""
    c = 2
    For m = lc To 1 Step -1
        r = Mid(xstr, m, 1) + 1
        result = result & Cells(r, c) & ", " ' Create result string
        c = c + 1
    Next m

     result = Left(result, Len(result) - 2)
    
    Cells(j + 1, 1) = result ' output RESULT to Column A
    
Next j

'Optimize Macro Speed
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Try this: RESULTS are in Column A and INPUT in column B onwards

[jeremypyle]

Hi John. Thanks so much for this! I can't seem to add cells further than row 12 or it will malfunction. I'm trying to paste a few hundred towns into column B. Do you know what I am doing wrong? If you try adding rows greater than 12 see what happens with you

[Glenn Kennedy]

Try this dynamic array:

=LET(c,B2:B10,rc,COUNTA(c),f,C2:C10,rf,COUNTA(f),a,D2:D10,ra,COUNTA(a),n,E2:E10,rn,COUNTA(n),x,rc*rf*ra*rn,INDEX(c,INT(SEQUENCE(x,,,1/(rf*ra*rn))))&", "&INDEX(f,1+MOD(INT(SEQUENCE(x,,,1/(ra*rn)))-1,rf))&", "&INDEX(a,1+MOD(INT(SEQUENCE(x,,,1/rn))-1,ra))&", "&INDEX(n,1+MOD(SEQUENCE(x)-1,rn)))

[kvsrinivasamurthy]

In A2 copied down. If additional items are added to any columns B, C, D, E automatically list will be updated. Formula should be copied down till blank cell is seen.

=IF(ROWS($A$2:$A2) <=PRODUCT($I$2,$J$2,$K$2,$L$2),TEXTJOIN(",",TRUE,IFERROR(INDEX(OFFSET($B$2,0,0,$I$2),1+INT((MOD(ROWS($A$2:$A2)-1,PRODUCT($I$2,$J$2,$K$2,$L$2)))/PRODUCT($J$2,$K$2,$L$2))),""),IFERROR(INDEX(OFFSET($C$2,0,0,$J$2),1+INT((MOD(ROWS($A$2:$A2)-1,PRODUCT($J$2,$K$2,$L$2)))/PRODUCT($K$2,$L$2))),""),IFERROR(INDEX(OFFSET($D$2,0,0,$K$2),1+INT((MOD(ROWS($A$2:$A2)-1,PRODUCT($K$2,$L$2)))/$L$2)),""),IFERROR(INDEX(OFFSET($E$2,0,0,$L$2),1+MOD((ROWS($A$2:$A2)-1),$L$2)),"")),"")

[John Vergara]

Hi to all!

Shot 2. Two options:

1. Generate the values in multiple columns. Later, join with TEXTJOIN (or using & depends on the number of columns).
If you extend the data, extend formulation for seeing results.

2. One single formula using MICROSOFT 365 INSIDER, using SCAN and BYROW.
You can extend the data. The formulation works automatically.

Blessings!