Search code examples
google-sheetsconcatenationtransposearray-formulasgoogle-sheets-query

Mix data of multiple columns into one column

I want to combine the content of three columns into one column like in this example:

Col1  Col2  Col3
A     1     x
B     2     Y

So the result is a column with 8 lines like this:

Result
A 1 X
A 1 Y
A 2 X
A 2 Y
B 1 X
B 1 Y
B 2 X
B 2 Y

I need a Google Sheets command to do it, not a Spreadsheet solution because the result values must change automatically when using the sheet.

I have already tried with COMBINE and JOIN without much success. If, alternatively, you show me how to combine only two of the three columns, I then could combine the third one in a second step.

Solution

  • 2 columns:

    =ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))
 &" "&TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9))))

    0

    3 columns:

    =ARRAYFORMULA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9)))&CHAR(9)),
 COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(C1:C&CHAR(9), COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9)))))), CHAR(9))))

    3 columns in two steps:

    =ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(CONCATENATE(D1:D&CHAR(9)), COUNTA(C1:C)), CHAR(9)))
 &" "&TRANSPOSE(SPLIT(CONCATENATE(REPT(C1:C&CHAR(9), COUNTA(D1:D))), CHAR(9))))

    0