Question: How do I filter values existing in one range but not in an other?

Answer:

Values-existing-in-range-1-but-not-in-range-2

Formula in B13:

=INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), MATCH(0, COUNTIF(Two, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), , 1))+COUNTIF(C12:$C$12, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), , 1)), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Formula in B21:

=INDEX(Two, MIN(IF((COUNTIF(One, Two)=0)+COUNTIF(B$20:$B20, Two)=1, ROW(Two)-MIN(ROW(Two))+1, "")), MATCH(0, COUNTIF(One, INDEX(Two, MIN(IF((COUNTIF(One, Two)=0)+COUNTIF(B$20:$B20, Two)=1, ROW(Two)-MIN(ROW(Two))+1, "")), , 1))+COUNTIF(B$20:$B20, INDEX(Two, MIN(IF((COUNTIF(One, Two)=0)+COUNTIF(B$20:$B20, Two)=1, ROW(Two)-MIN(ROW(Two))+1, "")), , 1)), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges

One (B2:D4)
Two (B7:E10)
What is named ranges?

Download excel example file.
Filter values existing in Range 1 but not in Range 2 in excel.xls
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) Returns the k-th smallest row number in this data set.

ROWS(array) returns the number of rows in a reference or an array

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

This blog article is one out of five articles on the same subject.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Filter text values existing in range 1 but not in range 2 using array formula in excel
  2. Filter common text values in range 1 and in range 2 using array formula in excel
  3. Filter unique values from a range using array formula in excel
  4. Filter common values between two ranges using array formula in excel
  5. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
  6. Filter unique rows and sort by date using array formula in excel
  7. Filter duplicate rows and sort by date using array formula in excel
  8. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  9. Extract unique values from a range using array formula in excel
  10. Filter a column and create a new unique list sorted from A to Z using array formula in excel