FilterDiff( )

Description

FILTERDIFF compares two filter arguments and returns rows from the first filter that are not in the second. The first parameter can be a filter or table. FILTERDIFF is useful for finding rows that are unique to a filter or a table compared to another filter.

Syntax

=FILTERDIFF(table/filter, filter formula)

Required

  • Table/filter : A table or filter formula that returns a list of rows. This is the filter from which we are finding a smaller subset of rows.
  • Filter formula: A filter that returns a list of rows. This is the filter whose rows we want to exclude in the result.

Example

In the Tasks table below, if the formula for completed tasks in B2 is:
=Filter(Tasks, "Tasks[Completed]=%", "Yes")

And the formula for Jorge’s tasks in B3 is:
=Filter(Tasks, "Tasks[Owner]=%", "Jorge")

To find Jorge’s tasks that are not completed, use:
=FILTERDIFF(B3,B2)

Returns the two tasks whose owner is Jorge and the completed status is NOT yes.

Another way to write the above formula without cell references is:
=FILTERDIFF(Filter(Tasks, "Tasks[Owner]=%", "Jorge"), Filter(Tasks, "Tasks[Completed]=%", "Yes"))

Returns the two tasks whose owner is Jorge and the completed status is NOT yes.

FILTERDIFF table image

Was this article helpful?
  • Yes
  • No

0 voters