0

I have a data base on Google sheet. See the spreadsheet at this link:https://docs.google.com/spreadsheets/d/1NGkH56Ga95osN8eBI6d5MhV7SnoEPK4Oe7ymRJVK7M0/edit#gid=1262146092 It s a copy so feel free to make any edit.

I am trying to filter information from the database using a query:

The filters are selected in the cell in yellow line 10 to 18 The query formula is in cell A22 and populates the lines and columns below. enter image description here I want the formula to return information from the database that matches (L10 Option 1 or L10 Opt 2 …. L10 Opt 5) AND (L11 Opt 1 or.. or.. L11 opt5) AND… and so on until that last line. If “all” is selected in option 1, then it doesn’t filter on that criteria.

The formula below works for all lines, but not if "all" is selected (written only for the 1st 2 lines below):

=query('6. Database'!1:190,"select A,B,D,F,G,H,I,J,K,L,M,AD,BA,CD,CQ,CR,CU,CY,DE,FF,FM,FS where (D = '"&C10&"' or D='"&D10&"' or D='"&E10&"' or D='"&F10&"' or D='"&G10&"') and (E = '"&C11&"' or E='"&D11&"' or E='"&E11&"' or E='"&F11&"' or E='"&G11&"') ")

I tried this but it didnt work (formula parse error): =query('6. Database'!1:190,"select A,B,D,F,G,H,I,J,K,L,M,AD,BA,CD,CQ,CR,CU,CY,DE,FF,FM,FS where ('"C10"'<>'all' and (D = '"&C10&"' or D='"&D10&"' or D='"&E10&"' or D='"&F10&"' or D='"&G10&"')) and ('"c11"'<>'all' and (E = '"&C11&"' or E='"&D11&"' or E='"&E11&"' or E='"&F11&"' or E='"&G11&"')) ")

The formula below work when "all" is selected but only for 1 line: =query('6. Database'!1:190,"select A,B,D,F,G,H,I,J,K,L,M,AD,BA,CD,CQ,CR,CU,CY,DE,FF,FM,FS " & if(C10="all",, "where (D = '"&C10&"' or D='"&D10&"' or D='"&E10&"' or D='"&F10&"' or D='"&G10&"') "))

How can I get it to work with the option to select “all” in any line ?

7
  • Welcome to Web Applications. As primarily opinion based questions are off-topic on this site is not a good idea to ask for "any recommendation".
    – Rubén
    Oct 25 '21 at 21:43
  • 1
    The formula parse error on the second formula occurs because & operand is missing in the following parts: - '"C10"'<>'all' it should be '"&C10&"'<>'all' - '"c11"'<>'all' it should be '"&c11&"'<>'all'
    – Rubén
    Oct 25 '21 at 21:48
  • 1
    Without access to the spreadsheet, data and layout, there is not really a way for the volunteer contributors here (or at least me) to help you with formula construction or suggestions beyond pointing out obvious errors (such as Rubén has done)—though even that can't be tested, as to whether or not it would solve your overall issue. If you care to share a link to your spreadsheet (or a copy of it), I think you will receive more engagement. (And it's likely that people would suggest entirely different approaches as opposed to what you're currently trying to do with QUERY.)
    – Erik Tyler
    Oct 25 '21 at 22:09
  • Thank you for your reply! I did the fix suggested by Ruben but still get a mistake. See the spreadsheet at this link: docs.google.com/spreadsheets/d/…. It s a copy so feel free to make any edit! Oct 26 '21 at 17:59
  • 1
    There are many more issues you're going to need to contend with here. For instance, what if someone selects "All" in one of the five selection cells OTHER THAN the first? (In my mind, that should STILL select "All" regardless of what else is selected.) You also have data in your data sheet that has nulls in columns where you have selectors. What did you intend to happen in these cases (because a blank doesn't match a selection)? These really go beyond this post and even this forum; but you'll need to carefully think through the logic.
    – Erik Tyler
    Oct 27 '21 at 3:25
0

I think this is easier to do with filter() with criteria consisting of regular expressions than a query() with several separate criteria cells.

Insert a new sheet a name it ColumnData. Get just the columns you are interested in with this formula in cell A1:

=query('6. Database'!A1:SJ, "select A, B, D, F, G, H, I, J, K, L, M, AD, BA, CD, CQ, CR, CU, CY, DE, FF, FM, FS", 2)

Then get the headers with this formula in '1. Project Selection'!A21:

={ ColumnData!A1:V1 }

Fetch the data with this formula in '1. Project Selection'!A22:

=iferror( 
  filter( 
    ColumnData!A2:V, 
    not( 
      mmult( 
        sign(not(iferror( 
          regexmatch( 
            trim(ColumnData!A2:V), 
            "(?i)" & A19:V19 
          ), 
          A19:V19 = "" 
        ))), 
        transpose(sign(column(A19:V19))) 
      ) 
    ) 
  ), 
  "(no matching data)" 
)

Clear rows 23 onward to make room for the results.

You can then write your column-by-column criteria in row 19 as regular expressions like 123|432 or new|improv. If you want to continue using the drop-down lists in A9:G17, you can also use formulas like this (example for cell C19):

=textjoin("|", true, A9:G9)

To learn the exact regular expression syntax used by Google Sheets, see RE2.

See the Elegant search spreadsheet for a working example. Click get your own copy to test it privately. Note that the example uses search() instead of regexmatch().

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.