CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Iman Ghavamabadi
Iman Ghavamabadi

Reputation: 47

Google Sheets: Use ArrayFormula for JoinText for multiple columns

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!

Upvotes: 0

Views: 323

Answers (2)

MattKing
MattKing

Reputation: 7773

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

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26806

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

Upvotes: 0

Related Questions