Question: How do I filter unique values from two ranges combined using array formulas?

Answer:

filter-unique-values-from-two-ranges

Array formula in B13:

=INDEX(_tbl1, MIN(IF(COUNTIF($B$12:B12, _tbl1)=0, ROW(_tbl1)-MIN(ROW(_tbl1))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(_tbl1, MIN(IF(COUNTIF($B$12:B12, _tbl1)=0, ROW(_tbl1)-MIN(ROW(_tbl1))+1)), , 1)), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary

Array formula in D13:

=INDEX(_tbl2, MIN(IF(COUNTIF($D$12:D12, _tbl2)+COUNTIF($B$13:$B$22, _tbl2)=0, ROW(_tbl2)-MIN(ROW(_tbl2))+1)), MATCH(0, COUNTIF($D$12:D12, INDEX(_tbl2, MIN(IF(COUNTIF($D$12:D12, _tbl2)+COUNTIF($B$13:$B$22, _tbl2)=0, ROW(_tbl2)-MIN(ROW(_tbl2))+1)), , 1))+COUNTIF($B$13:$B$22, INDEX(_tbl2, MIN(IF(COUNTIF($D$12:D12, _tbl2)+COUNTIF($B$13:$B$22, _tbl2)=0, ROW(_tbl2)-MIN(ROW(_tbl2))+1)), , 1)), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

The array formula in D13 and below automatically removes common unique distinct values between _tbl1 and _tbl2.

Named ranges
_tbl1 (B2:E5)
_tbl2 (B7:E10)
What is named ranges?

Download excel example file.
filter-unique-distinct-values-from-two-ranges.xlsx
(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 thirteen articles on the same subject "unique".

  • Share/Bookmark

Related posts:

  1. Highlight unique distinct values in two ranges combined using conditional formatting in excel
  2. Count unique values and unique distinct values in three ranges combined in excel
  3. Count unique values and unique distinct values in two ranges combined
  4. Filter unique distinct text values in a range using “contain” condition in excel
  5. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
  6. Extract a unique distinct list from two columns using excel 2007 array formula
  7. Filter unique distinct values where adjacent cells contain search string in excel
  8. Highlight duplicate values in two ranges combined using conditional formatting in excel
  9. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  10. Count unique distinct values in three columns combined in excel