4

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?

Improve this question
2
  • An array should work, can you try a text join and array instead?
    – Umar.H
    Oct 8, 2018 at 7:36
  • Can you give an example formula?
    – J H
    Oct 8, 2018 at 22:03

1 Answer 1

Reset to default
5

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.

Improve this answer
6
  • @Datanovice.I hadn't thought of using TEXTJOIN. That gets me closer, but still not quite there.
    – J H
    Oct 9, 2018 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, 2018 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!
    – Umar.H
    Oct 9, 2018 at 19:47
  • =ARRAYFORMULA(TEXTJOIN(", ", TRUE,QUERY(Sheet1!A2:E20,"Select B,C where A='"&A2&"'")))
    – J H
    Oct 9, 2018 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, 2018 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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