-1

I'm trying to query a group of say fruits so I'm grouped these into a comma-separated cell and I want to query that cell but I don't get any results. When I just one value in that cell says Apple it wants a result. I thought using contains would allow me to do that in a query? If not are they better methods?

Table

A B
Bob Apple
May Orange
Simon Apple
Sam Pear
Tom Grape

Query

=IFERROR(QUERY('Sheet1'!$A2:$AB500, "select A,C,Y where D contains 'Staff' and (K='Y' or L='Y') and C contains '"&G7&"' ", 0),"None")

G7 contains 'Apple,Pear,Grape,Orange'

Grimlockz
  • 2,541
  • 7
  • 31
  • 38

1 Answers1

1

Try changing this part

C contains '"&G7&"'

to

C matches '"&SUBSTITUTE(G7, ",", "|")&"'

and see if that helps?

EDIT: if you have brackets in the range and in G7 try

 =ArrayFormula(IFERROR(QUERY({A:C, regexreplace(B:B, "\(|\)",)}, "select Col1 where Col3 contains 'Team' and Col4 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "\(|\)",), ",", "|")&"' ", 0),"None"))

and see if that works?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • Works like a charm – Grimlockz Nov 23 '18 at 10:38
  • It doesn't like brackets for G7 if you apple (red), apple (green), Orange,..., etc it doesn't pick up any of the cells with the brackets – Grimlockz Nov 27 '18 at 14:25
  • That is because brackets represent a capturing group in a regular expression. You can remove those brackets by replacing those brackets:: C matches '"&SUBSTITUTE(REGEXREPLACE(G7, "\(|\)"; ), ",", "|")&"' – JPV Nov 27 '18 at 15:17
  • Thanks - that does make sense with it being an escaping parenthesis but I'm still getting the same results – Grimlockz Nov 27 '18 at 15:25
  • Seems something went wrong in pasting. I added the part in my post. Please check it again. – JPV Nov 27 '18 at 18:16
  • Thanks for looking into this again - Sadly it's the same results so not getting the brackets – Grimlockz Nov 28 '18 at 09:15
  • The brackets are in G7 or also in the range? Can you maybe share a spreadsheet with some sample data? – JPV Nov 28 '18 at 09:26
  • sure - https://docs.google.com/spreadsheets/d/1SSw9nRanpFtWh42eBhz1JeMHpZKZlrhEE52RQkPZ0V8/edit?usp=sharing – Grimlockz Nov 28 '18 at 09:30
  • Updated the formula (see last one in my post). See if that works? – JPV Nov 28 '18 at 15:50
  • Thanks for trying again @JPV - Don't think anything has changed in the results and not sure if the post got updated? – Grimlockz Nov 28 '18 at 15:56
  • Don't know what happened. Maybe I forgot to confirm the edit. Anyhow, the formula should be visible now. – JPV Nov 29 '18 at 08:17
  • If the forumla is based on other sheets it stops working - {code} =ArrayFormula(IFERROR(QUERY({Sheet2!A:C, regexreplace(Sheet2!B:B, "\(|\)",)}, "select Col1 where Col3 contains 'Team' and Col2 matches '"&SUBSTITUTE(REGEXREPLACE(D2, "\(|\)",), ",", "|")&"' ", 0),"None")) {code} . - I've updated the spreadsheet with it L18 - where would the forumla break here? Thanks – Grimlockz Nov 30 '18 at 09:52
  • can you use the same formula between sheets? I wouldn't have thought pulling data from other sheets would break the regex? – Grimlockz Dec 04 '18 at 09:25
  • 1
    In your new formula, change Col2 to Col4.. that should do it. – JPV Dec 04 '18 at 12:51
  • Awesome - Interesting doesn't seem to like Col10 and above as in B20 – Grimlockz Dec 04 '18 at 17:26
  • Col10 ? Your current range is only 4 columns wide (A:C and B:B). – JPV Dec 04 '18 at 17:50
  • Sorry @JPV - Sheet didn't update but it's updated now – Grimlockz Dec 04 '18 at 17:52
  • As mentioned above, you need to change the column where you want to find the match for the values in F3. Give the range A:M and the added column regexreplace(Sheet2!B:B, "\(|\)",) you would now have to refer to column 14 (13 columns in the range A:M + 1 column with the regexreplace in the array. – JPV Dec 04 '18 at 18:08