Google Sheets: Use ArrayFormula for JoinText for multiple columns

Question

I want to Use ArrayFormula for JoinText for multiple columns which have their own ArrayFormulas in Google Sheets. My formula works for columns that have plain text values but for some reason Google Sheets gives me an error when I apply it to columns that have their own ArrayFormulas applied to them.

Take a look at the example sheet I've created HERE

Any help will be appreciated!

Which tab are you trying to work on? and what is the desired result? — MattKing
– MattKing, Commented May 25, 2020 at 14:33

ziganotschka · Accepted Answer · 2020-05-26 11:33:34Z

0

I hope this solution with query is helpful for you:

=ArrayFormula(transpose(query(transpose(C1:D),,2)))

(Will work faster if you know the limit of the range, e.g. C1:D10).

For implementing commas between the columns you can use

=ARRAYFORMULA(IF(C1:C="",D1:D,if(D1:D="",C1:C,C1:C&", "&INDIRECT("D1:D"))))

edited May 26, 2020 at 11:33

answered May 25, 2020 at 15:01

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Iman Ghavamabadi Over a year ago

Thanks for the answer but it's very slow and my sheet is very long. What about the comma between the cell values?

ziganotschka Over a year ago

I see, I added another formula which implements the comma. I hope this is more helpful.

MattKing · Accepted Answer · 2020-05-28 15:47:37Z

0

I put this formula in cell I2 on your sample sheet. It should be relatively fast for many thousands of rows.

=ARRAYFORMULA(SUBSTITUTE(TRIM(C2:C&CHAR(10)&D2:D),CHAR(10),", "))

edited May 28, 2020 at 15:47

answered May 25, 2020 at 15:46

MattKing

7,80310 silver badges13 bronze badges

4 Comments

Iman Ghavamabadi Over a year ago

Thanks for the answer but I think you gotta revisit the question. I don't wanna deal with column A and B directly. I'm focusing on column C and D.

MattKing Over a year ago

I saw that, i figured that you didn't need C and D if I just did it all using A and B since it looked like C and D were just being used to construct the joined text columns and I could just skip right to that from A and B directly

Iman Ghavamabadi Over a year ago

That's the point. I've also mentioned that in the question which says that I don't wanna use A and B, I wanna use C and D. The reason I don't wanna use your formula is that It's hard coding the content of A cells because cell data can change in the future. Would you be able to modify it?

MattKing Over a year ago

I see, ok, I modified my formula on the sheet as well has here in this post.

Collectives™ on Stack Overflow

Google Sheets: Use ArrayFormula for JoinText for multiple columns

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related