Article updated on February 02, 2018

Question: How do i remove common values between two lists?


Answer: The solution in this article, removes common values and the original values, as seen on the picture below. To create a unique list from two columns or two cell ranges, check this article out: Create unique list from two columns


Excel 2007 array formula in cell D1:

=IFERROR(INDEX($A$1:$A$20, SMALL(IF(COUNTIF($B$1:$B$21, $A$1:$A$20)=0, ROW($A$1:$A$20)-MIN(ROW($A$1:$A$20))+1, ""), ROW(A1))), INDEX($B$1:$B$21, SMALL(IF(COUNTIF($A$1:$A$20, $B$1:$B$21)=0, ROW($B$1:$B$21)-MIN(ROW($B$1:$B$21))+1, ""), ROW(A1)-COUNTIF($B$1:$B$21, $A$1:$A$20)))) + CTRL + SHIFT + ENTER.

Download excel sample file for this tutorial.
(Excel 2007 Workbook *.xlsx)

Functions in this tutorial:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Counts the number of cells in a range that contain numbers

Counts the number of cells within a range that meet the given condition

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 number in this data set.

returns the row number of a reference

returns the column number of a reference

This blog article is one out of five articles on the same subject.