Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct list? Merge two list without duplicates, in other words.

unique-distinct-list-from-two-columns1

Answer:

unique-distinct-list-from-two-columns2

Excel 2007

Formula in C2:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "") + CTRL + SHIFT + ENTER copied down to C20.

Earlier versions of Excel

Formula in C2:

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0)), INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))) + CTRL + SHIFT + ENTER copied down to C20.

The ranges don´t have to be adjacent, these formulas work fine anyway.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

how-to-extract-a-unique-list-from-two-columns-in-excel-2007.xlsx
(Excel 2007 Workbook *.xlsx)

how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

This blog article is one out of thirteen articles on the same subject "unique".

  • Share/Bookmark

Related posts:

  1. Extract a list of duplicates from two columns combined using array formula in excel
  2. Extract a unique distinct list from three columns in excel
  3. Extract a list of duplicates from three columns combined using array formula in excel
  4. Extract largest values from two columns using array formula in excel
  5. Comparing two columns and sum unique values using array formula in excel
  6. Create unique list from two columns
  7. Unique distinct values from multiple columns using array formula
  8. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
  9. Unique distinct list from a column sorted A to Z using array formula in excel
  10. Extract a list of duplicates from a column using array formula in excel