Search code examples
google-sheetsgoogle-sheets-formula

How can I make this formula more dynamic by including another criteria (cell F11) in the query?

This formula currently queries data based on a criteria. How can I include another criteria, which will result in a subset of the filtered by either both (C11 and F11) or one of them.

=if(C11<>"";
QUERY(Dados!A1:V;"select A, B, E, L, ' ', M, N, O, G, F, P, Q, R, T, I, J where H matches '"&C11&"'");
QUERY(Dados!A1:V;"select A, B, E, L, ' ', M, N, O, G, F, P, Q, R, T, I, J where A is not null label ' ' ' '"))

I guess it would be map based, but that is too new to me and I would appreciate some help.

Here is the data and the expected result in the green background table:

enter image description here

...and here is the file, in cas you fell like giving it a shot.

Solution

  • You have sales data including "Product", "Company" and "Lota" (Load) and you want to query the data based on any combination of "Product" and/or "Company" and/or "Lote".

    Consider this answer (applying an idea expressed by @MikeSteelson in How to use query to filter data based on certain conditions in google sheet).

    • Create two new sheets: "Search Results" and "Master List"

    • Create selection options by building Validation Rules on "Search Results"

      • In cells B2, B3 and B4 of "Search Results", create Validation Rules based on ranges in "Master List". Note that each rule includes an option = "Not filtered".
      • "Master List"
        • "Products:" Cell A2: =query({"Not filtered";'Página21'!A4:A13};"select *")
        • "Companies:" Cell C2=query({"Not filtered";'Página21'!B4:B13};"select *")
        • "Lote:" Cell E2 =query({"Not filtered";'Página21'!D4:D13};"select *")

    • Build IF statements on "Search Results"

      • Cell C2: =if(B2="Not filtered";;"Col1 matches '"&B2&"' ")
      • Cell C3: =if(B3="Not filtered";;"Col2 matches '"&B3&"' ")
      • Cell C4: =if(B4="Not filtered";;"Col4 matches '"&B4&"' ")
      • You can hide these formulas by setting the font colour to white.

    • Build the query formula on "Search Results"

      • In cell A6 =QUERY({'Cópia de Página21'!A3:E13};"Select Col1, Col2, Col3, Col4, Col5 where Col1 is not null and "&textjoin("and ";true;C2:C4)&" ";1)
      • select a "Product", "Company" or "Lote", or any combination;
      • where you don't want the "Product", Company" or "Lote" to be a criteria, select "Not filtered" for that option.

    Search Results - one criteria

    Search Results

    Search Results - two criteria

    two criteria

    Master List - Validation Rules

    Validation Rules