Mix data of multiple columns into one column

Question

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.

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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))))

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))))

Your solutions create new synapses in my brain! This is something amazing. This is a good approach I think. — contributorpw, Feb 25 '19 at 06:49
@oshliaer in case you need some more synapses to be created click here: https://webapps.stackexchange.com/a/125018/186471 — player0, Feb 25 '19 at 09:05

pnuts · Answer 2 · 2019-02-25T01:08:38.433

1

Assuming A is in A1, in Row1 and copied down to suit:

=offset(A$1,int((row()-1)/4),)&" "&offset(B$1,abs(iseven(int((row()-1)/2))-1),)&" "&offset(C$1,mod((row()-1),2),)

edited Feb 25 '19 at 01:08

answered Feb 25 '19 at 00:38

pnuts

58,317
11
87
139

1

the output is without spaces (if it matters) – player0 Feb 25 '19 at 01:04

contributorpw · Answer 3 · 2019-02-25T06:41:44.203

0

Rotate, join, rotate, join then etc. There are no other ways to reach this. If I had such a task, I would try to multiply N matrices N-1 times.

=ARRAYFORMULA(TRANSPOSE(SPLIT(
  TEXTJOIN("·",TRUE,SPLIT(
    TEXTJOIN("·",TRUE,(A2:A3&" "&TRANSPOSE(B2:B3))),
    "·")&" "&C2:C3),
  "·"
)))

edited Feb 25 '19 at 06:41

answered Feb 25 '19 at 06:10

contributorpw

4,739
5
27
50

Mix data of multiple columns into one column

3 Answers3

2 columns:

3 columns:

3 columns in two steps:

Linked