create-unqiue-list-from-two-columnsI read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you can use to merge two lists into a third list and prevent duplicate entries in the resulting list,  using VBA to create a macro.

Here is a solution to create a unique list from two columns without using VBA. To the right are two pictures of two example lists (List1 and List2).

create-unqiue-list-from-two-columns2

Named ranges
I created a named range for List 1: List1 (A2:A5)
I created a named range for List 2: List2 (C2:C6)

The picture below shows the unique list and the unique distinct list.

Create unique list from two columns1

Unique values

Here is the formula in A10:

=IF(ROWS(A9:$A$9)<=SUM(IF(COUNTIF(List1, List1)+COUNTIF(List2, List1)=1, 1, 0)), INDEX(List1, MATCH(0, IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))*NOT(COUNTIF(A9:$A$9, List1))=1, 0, COUNTIF(List1, List1)+COUNTIF(List1, List2)), 0)), INDEX(List2, MATCH(0, IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))*NOT(COUNTIF(A9:$A$9, List2))=1, 0, COUNTIF(List2, List2)+COUNTIF(List1, List2)), 0))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Unique distinct values

Here is the formula in C10:

=IF(ROWS(C9:$C$9)<=SUM(1/COUNTIF(List1, List1)), INDEX(List1, MATCH(0, COUNTIF(C9:$C$9, List1), 0)), INDEX(List2, MATCH(0, COUNTIF(C9:$C$9, List2), 0))) + CTRL + SHIFT + ENTER copied down as far as necessary.

How to customize the formula to your excel workbook
Change the named ranges. If your unique list starts at F3, change C9:$C$9 (or A9:$A$9)  in the above formula to $F$2:F2.

Download excel sample file for this tutorial.
unique-list-from-two-columns.xls
(Excel 97-2003 Workbook *.xls)

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

ROW(reference) returns the rownumber of a reference

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

ADDRESS(row_num,column_num)
creates a cell reference as text, given specified row and column numbers

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

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

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

How to extract a unique distinct list from a column in excel
Extract a unique distinct list from two columns using excel 2007 array formula
Extract a unique distinct list from three columns in excel

Extract distinct unique sorted year and month list from a date series in excel
Create a unique distinct list from a date range in excel

Unique values from multiple columns using array formulas
Extract a unique distinct list sorted from A-Z from range in excel
Sort a range by occurence using array formula in excel
Filter unique distinct values from two ranges combined in excel 2007

Create a unique list and sort by occurrances from large to small
Unique list to be created from a column where an adjacent column has text cell values
Create unique list from column where an adjacent column meets criteria
How to create a unique distinct list where other columns meet two criteria

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Extract a unique distinct list from two columns using excel 2007 array formula
  2. Comparing two columns and sum unique values using array formula in excel
  3. Extract a unique distinct list from three columns in excel
  4. How to create a unique distinct list where other columns meet two criteria
  5. Extract largest values from two columns using array formula in excel
  6. Count unique distinct values in two columns with date criteria in excel
  7. Extract a list of duplicates from two columns combined using array formula in excel
  8. Count unique distinct values in three columns combined in excel
  9. Count unique distinct values in two columns in excel
  10. Merge two columns into one list in excel