Combing Filter Query with Concatenate in Google Sheets

0
votes

I have the following Query that pulls data from another spreadsheet into a daily run sheet to be issued to our drivers. However we are finding there are too many columns making the print too small to read when asked to fit one page wide. So I would like to combine a couple of columns in a concatenate.

The current formula is: =QUERY( Filter(Jobs_Database,Start_Date=E2,Assigned_To=E1), "Select Col1,Col2,Col3,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col20")

This is a picture of the spreadsheet. enter image description here

I would like to concatenate/join columns "F to L" using a - or , as a spacer. Is this possible?

Thank you :)

1
google-sheets

1 Answers

0
votes

Let's say your data was like my image below and you wanted to select col A, then concatenate F:L, then select col T...

enter image description here

Add this to cell V1:

=arrayformula(query({A:E,flatten(query(transpose(IF(F:L<>"",F:L&",",)),"",MAX(IF(A:T<>"",ROW(A:A))))),M:T},"select Col1,Col2,Col3,Col6,Col14",1))

You basically take advantage of a quirk in query that collapses columns when the query 'header' is a big number MAX(IF(A:T<>"",ROW(A:A))). The separator is shown as a comma in F:L&",".

If it works for you, I'll add a detailed breakdown of what's happening.

Please consider https://stackoverflow.com/help/someone-answers :-)