## How to remove common values between two columns

*Article last updated on August 12, 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 above.

Array formula in cell F3:

To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

### Explaining formula in cell F3

#### Step 1 - Count values

The COUNTIF function lets you count values based on a condition or criteria. COUNTIF(*range*, *criteria*) The first argument is the cell range you want to evaluate.

The criteria argument contains the values you want to look for.

COUNTIF($D$3:$D$12,$B$3:$B$12)

becomes

COUNTIF({"AA"; "BB"; "VV"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"},{"AA"; "BB"; "CC"; "DD"; "EE"; "PP"; "GG"; "HH"; "II"; "JJ"})

and returns the following array {1;1;0;1;1;0;1;1;1;1}.

The image to the right shows the array and the corresponding values, you can now easily see that CC and PP are not found in List 2.

#### Step 2 - Check if value is 0 (zero) and if so return the corresponding row number

If the array contains a zero the corresponding value is not found in the other list. The IF function lets you build a logical expression, if it evaluates to TRUE on thing happens and if FALSE another thing happens.

IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), "")

becomes

IF({1;1;0;1;1;0;1;1;1;1}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns the following array:

{"";"";3;"";"";6;"";"";"";""}

The image to the right shows the row numbers (relative to cell range $B$3:$B$12) in column C .

#### Step 3 - Extract the k-th smallest row number

The SMALL function returns the k-th smallest number from a cell range or an array. This allows us to extract a single value in a cell each.

SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, ROWS($A$1:A1))

becomes

SMALL({"";"";3;"";"";6;"";"";"";""}, 1)

and returns 3.

#### Step 4 - Get the corresponding value

The INDEX function returns a value based on a row and column number, the column number is not necessary if the cell range only has one column.

INDEX($B$3:$B$12, SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0, MATCH(ROW($B$3:$B$12),ROW($B$3:$B$12)), ""), ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$12, 3)

and returns the value in cell B5 which is "CC" to cell F3.

#### Step 5 - Return values from List 2

When there are no more values to extract from List 1 the IFERROR function lets you continue with List 2.

The formula is repeated in the second argument except that other cell ranges are used. I am not going to explain these steps again, see above steps if you need to.

The fomula in cell F5 becomes

IFERROR(INDEX($B$3:$B$12, SMALL(IF(COUNTIF($D$3:$D$12, $B$3:$B$12)=0,ROW($B$3:$B$12)-MIN(ROW($B$3:$B$12))+1,""),ROW(B1))),INDEX($D$3:$D$12, SMALL(IF(COUNTIF($B$3:$B$12,$D$3:$D$12)=0, ROW($D$3:$D$12)-MIN(ROW($D$3:$D$12))+1, ""), ROW(B1)-SUM((COUNTIF($D$3:$D$12,$B$3:$B$12)=0)+0))))

becomes

IFERROR(#NUM,INDEX($D$3:$D$12, SMALL(IF(COUNTIF($B$3:$B$12,$D$3:$D$12)=0, ROW($D$3:$D$12)-MIN(ROW($D$3:$D$12))+1, ""), ROW(B1)-SUM((COUNTIF($D$3:$D$12,$B$3:$B$12)=0)+0))))

becomes

=IFERROR(#NUM,INDEX($D$3:$D$12, 3))

and returns "VV" in cell F5.

### Download Excel *.xlsx file

remove-common-values-between-two-columns3.xlsx

To create a unique list from two columns or two cell ranges, check this article out: Create unique list from two columns

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Filter common values from three columns

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]

Compare two tables: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Sean asks: How would you figure out an unique list where the sum of in one column doesn't match the […]

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

### One Response to “How to remove common values between two columns”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[...] have a look at this link ad eventually the related pages __________________ Happy with the answer ? Click on the scale [...]