Author: Oscar Cronquist Article last updated on February 13, 2018

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 in column F.

Unique values

Formula in cell F3:

=IFERROR(LOOKUP(2, 1/((COUNTIF(F2:$F$2, $B$3:$B$6)+COUNTIF($D$3:$D$7, $B$3:$B$6)+COUNTIF($B$3:$B$6, $B$3:$B$6))=1),$B$3:$B$6), LOOKUP(2,1/((COUNTIF(F2:$F$2, $D$3:$D$7)+COUNTIF($B$3:$B$6, $D$3:$D$7)+COUNTIF($D$3:$D$7, $D$3:$D$7))=1), $D$3:$D$7))

Download excel sample file for this tutorial

unique list from two columnsv2.xlsx