1

In a Google Sheet I have the following formula:

=CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'"))

This currently returns each pair of results in sequence (with no spaces) and in a single cell, as in (assuming 3 results on sheet "Summary"):

A1B1A2B2A3B3

But I would like the results returned separated, as in:

A1/B1, A2/B2, A3/B3

I tried using JOIN as in:

=JOIN(", ",(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'")))

But this returns the following error:

JOIN range must be a single row or single column.

I tried exploring various solutions (e.g. using array formulas and also using SPLIT), but I can't get the result I want.

Anyone?

  • An array should work, can you try a text join and array instead? – Datanovice Oct 8 '18 at 7:36
  • Can you give an example formula? – J H Oct 8 '18 at 22:03
1

Try =TEXTJOIN

=ARRAYFORMULA(TEXTJOIN(" , ", TRUE,QUERY(A1:C4, "Select *"),0 ))

I'm not sure why JOIN doesn't work but I find TEXTJOIN works a little better.

  • @Datanovice.I hadn't thought of using TEXTJOIN. That gets me closer, but still not quite there. – J H Oct 9 '18 at 18:55
  • Meant to also add: The following (slightly modified from what you suggested): =ARRAYFORMULA(TEXTJOIN(", ", TRUE,QUERY(Sheet1!A1:E20,"Select B,C where A="&A2&""))) Returns each pair of (B & C) results in sequence separated by the TEXTJOIN specified delimiter (in this case a comma), but there is no delimiter to distinguish one pair from another, i.e. results show as: , ,A1,B1,A2,B2,A3,B3, Rather than (what I’d like): A1/B1, A2/B2, A3/B3 Also, note that curiously the results start with a pair of white spaces separated by a comma. – J H Oct 9 '18 at 19:06
  • Can you split A1B1 then reapply a new delimiter? Can you make a spreadsheet with your desired output? I'm having a hard time putting a picture to your issue! – Datanovice Oct 9 '18 at 19:47
  • =ARRAYFORMULA(TEXTJOIN(", ", TRUE,QUERY(Sheet1!A2:E20,"Select B,C where A='"&A2&"'"))) – J H Oct 9 '18 at 21:14
  • Also... =ARRAYFORMULA(TEXTJOIN(", ", TRUE,QUERY(Sheet1!A2:E20,"Select B,C where A='"&A2&"'"))) Gives B1,C1,B2,C2,B3,C3, but I want B1/C1, B2/C2, B3/C3 (i.e. both collate and separate the query results and output to a single cell). Using TEXTJOIN gets closer (i.e. before I had C1B1C2B2C3B3), but the pairs are still not distinguished from one another. I don't know how to add a SPLIT function to the formula that would also split by every second delimiter. Example sheet I made to illustrate: docs.google.com/spreadsheets/d/… – J H Oct 9 '18 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.