2

I just discovered the query function and it's a huge game-changer. I've been tinkering a little but would love some help with this one. What I would like to do is

=query(
  'Sample Data'!A3:D10, 
  "select A, C where (B='neighborhood')" and "select D where (D='cuisine')"
)

Even if D is populated, it is irrelevant for this query for any other value so I'd like to leave the cell blank in that case.

Is this possible? I've tried a few options and left them in the sample sheet [linked below] to tinker with

I made a quick Sheet of sample data as requested and in the process I thought of a better sample use-case. Sorry for the confusion changing the description, but I think this use case makes more sense to more people.

  • @Rubén: Edited the OP to add an example and hopefully clarify. – miststlkr Aug 28 '16 at 3:50
  • No, that would show A,B when both C<>'' and D='physics'. I want A and B when c<>' ' and D only when D='physics' – miststlkr Aug 28 '16 at 6:08
  • hmm... that gives a "Formula Parse Error" but gives me a few other ideas to try. If I concatenate the two query formulas with an & between the two, it kinda does what I want but stops after the first hit, and consolidates the fields into one cell as a string which is not ideal either, but perhaps a thread to follow? – miststlkr Aug 28 '16 at 17:09
  • Let us continue this discussion in chat. – miststlkr Aug 28 '16 at 17:17
  • @Rubén: Sample data as requested. A Sheet with some sample data, plus an example of the desired output. Sorry for the delay, your time is appreciated. docs.google.com/spreadsheets/d/… – miststlkr Aug 31 '16 at 5:06
0

Short answer

Make two queries and use IF, IERROR, ARRAYFORMULA and the array handling feature of Google Sheets.

Extended answer

It's not possible to get the desired result using a single QUERY because of the Google Query implementation. Fortunately the desired result could be obtained by using other Google Sheets features.

From the linked spreadsheet in the question

What I would like is a single query that would let me output all of the restaurants in a given neighborhood.
Then for each result, IF the restaurant matches a certain cuisine then display the cuisine. if it does not, then leave the cell blank. Bonus points for sorting the output with desired cuisine on top, but that may be asking too much and is really not important. So if I am in Strathcona and hungry, I can get a list of all the places I've "been meaning to go to" in the area, and tonight I'm in the mood for Italian so:

I would like this output:

Poor Italian    1816 Howard St  Italian  
Lucy's          15 Main   
Parallel 49     123908 Main 

The following formula returns the desired output:

=ArrayFormula(
  {
    query('Sample Data'!A3:F10,"select A,C where B = 'Strathcona'"),
    if(
      query('Sample Data'!A3:F10,"select D where B = 'Strathcona'")="Italian",
      "Italian",
      IFERROR(1/0)
    )
  }
)

Remarks: In order to make simple formula the neighbourhood and cuisine are "hardcoded" in the formula. In order to make the formula more easy of maintain, this values could be replaced by cell references but you should be aware that the query argument requires that strings be enclosed by using apostrophes.

Assume that A1 holds the value of the neighbourhood. In the formula, the "hardcoded" value 'Strathcona' could be replaced by '&A1&'

By the other hand, the case of the cuisine is out of the query argument, so it could be replaced by just the cell reference. Assume that B1 holds de the value of cuisine. In the formula, the "harcoded" value "Italian" could be replaced by B1

References


Note to self

  • 1
    I have some more reading to do for ArrayFormula now in order to understand it fully, but that did work as intended. I very much appreciate your time. – miststlkr Aug 31 '16 at 18:07

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.