Filter
The Filter function gives a subset of data from a specified range...
Google Reference
Google Reference
- https://support.google.com/docs/answer/3093197?hl=en
- FILTER(range, condition1, [condition2, ...])
- =filter(A2:B26, A2:A26 > 5, D2:D26 < 10)
- =filter(A2:C5, {TRUE; TRUE; FALSE; TRUE}) (I've never used this before... not sure how applicable)
- =filter(A2:B10, NOT(ISBLANK(A2:A10)))
- =filter('4_Contacts'!A:L,'4_Contacts'!B:B="Doctor")
- =filter({'4_Contacts'!B:B,'4_Contacts'!M:M,'4_Contacts'!R:R,'4_Contacts'!R:R,'4_Contacts'!N:N} ,'4_Contacts'!A:A = D35) // create a composite table/array to then filter on -- this becomes much more like query this way but unlike query it remains dynamic as the source table cols change...
- You can filter info on another worksheet -- Filter('Patients'!A2:B26, 'Patients'!A2:A26 > 5)
- The conditions do NOT have to be included in the range... Filter('Patients'!B2:B26, 'Patients'!A2:A26 > 5) -- this is a key feature that is not covered well...
- You can NOT easily change the order of the columns returned in a filter call, this the order of the data table or source is very relevant for use elsewhere..
Other Examples
Other Examples/Comments
- Example with named ranges
- =filter(salesLedgerTable,salesLedgerCol1 = dateMonday)
- Example without named ranges..
- =filter('sales ledger'!A:F,'sales ledger'!A:A = dateMonday)
- =filter(stHeader0!A:F, stHeader0!B:B = "Bob Jones") // double quotes required, single no work..
- DATA FORMATS CARRIED IN.. WITH QUERY THEY ARE NOT...
- AS COMPARED TO QUEARY...
- =query(stHeader0!A4:F,"Select * where B = 'Bob Jones'")
- THE FILTER RESULTS KEEP THEIR FORMATTING AND THEY DON'T WITH QUERY.....
- === DYNAMICALLY FILL FILTER PARAMETERS (most seemingly haven't figured this out yet..) ====
- n13 = stDetails0!C:K //can dynamically build this with concatenate
- n14 = stDetails0!K:K //can dynamcially build this with concatenate
- n15 = 1482548007 // don't need to add quotes for text via this method.
- =filter(indirect(N13), indirect(N14) = M14)
- http://stackoverflow.com/questions/30375508/filter-function-based-on-dynamic-criteria-from-another-cell but nobody had the good/simple answers...
- === And/or in filter clause ===
- By default the equation is filter(range,condition1, condition2, condition3, etc)...but these are "Ands", can't be dynamically built and hard to dynamically fill if the operator (<,>,=,<>, isBlank) is not the same for the varying conditions...
- + = OR and * AND...
- https://productforums.google.com/forum/#!msg/docs/8nStFrjgO5Q/S0ppgEicgcAJ
==== DATE IS A TRAINWRECK ======
- no isDate function... instead, not(iserror(datevalue(txnsTableDate)))
- likewise, need to reformat date for use in filter...
- =filter(txnsTable, not(iserror(datevalue(txnsTableDate))), txnsTableDate > DATE(1950,1,1))
- A1 > Date(yyyy,MM,dd)
- === Criteria Examples.... ====
- =filter(soapTable,soapTableColE=C3,soapTableColA>C5,soapTableColA<if(isblank(C6),today()+1,C6))
- = filter(extendedNotesTable,extendedNotesColB=C3,extendedNotesColA>C5,extendedNotesColA<if(isblank(C6),today()+1,C6))
=== Other Examples ===
= filter(
patientVisitsTable,
patientVisitsTableColB=C3,
patientVisitsTableColA>C5,
patientVisitsTableColA<if(isblank(C6),today()+1,C6),
if(
isblank(C7),
NOT(IsBlank(patientVisitsTableColN_eobStatus)),
patientVisitsTableColN_eobStatus = C7
)
)
= filter(
patientVisitsTable,
if(
isblank(C3),
NOT(IsBlank(patientVisitsTableColN_eobStatus)) *Row(patientVisitsTableColN_eobStatus)<>1,
patientVisitsTableColN_eobStatus = C3
)
)
= filter(patientVisitsTable,patientVisitsTableColG_ni="x",patientVisitsTableColN_eobStatus<>"9 - Recieved and OK")
= filter(eobLineItemsTable,eobLineItemsTableColA=C3,eobLineItemsTableColK="2 - Patient Resp.")
= filter(eobLineItemsTable,eobLineItemsTableColA=C4,eobLineItemsTableColB>C6,eobLineItemsTableColB<if(isblank(C7),today()+1,C7))
= filter(
patientVisitsTable,
patientVisitsTableColB=C3,
patientVisitsTableColA>C5,
patientVisitsTableColA<if(isblank(C6),today()+1,C6),
if(
isblank(C7),
NOT(IsBlank(patientVisitsTableColN_eobStatus)),
patientVisitsTableColN_eobStatus = C7
)
)
= filter(
patientVisitsTable,
if(
isblank(C3),
NOT(IsBlank(patientVisitsTableColN_eobStatus)) *Row(patientVisitsTableColN_eobStatus)<>1,
patientVisitsTableColN_eobStatus = C3
)
)
= filter(patientVisitsTable,patientVisitsTableColG_ni="x",patientVisitsTableColN_eobStatus<>"9 - Recieved and OK")
= filter(eobLineItemsTable,eobLineItemsTableColA=C3,eobLineItemsTableColK="2 - Patient Resp.")
= filter(eobLineItemsTable,eobLineItemsTableColA=C4,eobLineItemsTableColB>C6,eobLineItemsTableColB<if(isblank(C7),today()+1,C7))