I 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.

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

Create unique list from two columns1

Named ranges
List1 (A2:A5)
List2 (C2:C6)

Unique values

Here is the  array 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)))

Unique distinct values

Here is the array 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)))

How to create an array formula

  1. Select cell C10
  2. Copy/Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula in cell C10

  1. Select cell C10
  2. Copy (Ctrl + c)
  3. Selelct cell range C11:C15
  4. Paste (Ctrl + v)

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.

Possible blanks in the two lists

Array formula in c10:

=IF(ROWS(C9:$C$9)<=SUM(IF(COUNTIF(List1, List1)=0, 0, 1/COUNTIF(List1, List1))), INDEX(List1, MATCH(0, IF(List1="", 1, COUNTIF(C9:$C$9, List1)), 0)), INDEX(List2, MATCH(0, IF(List2="", 1, COUNTIF(C9:$C$9, List2)), 0)))

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

creates a cell reference as text, given specified row and column numbers

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