Google Sheets: Use ArrayFormula for JoinText for multiple columns

0
votes

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!

2
google-sheetsgoogle-sheets-formulaspreadsheet
Your sheet is protected. Also what is the desire result? – Rocky
Which tab are you trying to work on? and what is the desired result? – MattKing
@Rocky Just updated the sheet. Please have a look. – Iman Ghavamabadi
@MattKing - Just updated the sheet. Please have a look – Iman Ghavamabadi

2 Answers

0
votes

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

0
votes

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