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

remove-common-values.png

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

remove-common-values_1.png

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.
remove-common-values-between-two-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this tutorial:

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

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

COUNTIF(range;criteria)
Counts the number of cells within a range that meet the given condition

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

VALUE(text) converts a text string that represents a number to a number

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

ROW(reference)
returns the row number of a reference

COLUMN(reference)
returns the column number of a reference

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