## Compare two columns and show differences

**Array Formula in B11:**

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell D2 and paste it down as far as needed.

**Array Formula in B15:**

Copy cell D9 and paste it down as far as needed.

### Sorted from A to Z

**Array Formula in B11:**

**Array Formula in B15:**

### Blog posts you may find interesting:

How to find common values from two lists

How to remove common values between two columns

Filter common values between two ranges using array formula in excel

Filter values existing in range 1 but not in range 2 using array formula in excel

Highlight common values in two lists using conditional formatting in excel

**Download excel *.xlsx file**

Compare and filter values in two lists.xlsx

Compare and filter values in two lists.xls

(Excel 97-2003 Workbook *.xls)

Extract shared values between two columns

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: Filter common records

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

Compare two tables: Remove common records

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

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 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 many common cell values are there in list 1 and 2?

Question: How many common cell values are there in list 1 and 2? Answer: Here is how to count matching […]

### 4 Responses to “Compare two columns and show differences”

### 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

Oscar here's another variation to this, a much smaller one I guess.

Values in List 1 and not in List 2

=INDEX(List_1, SMALL(IF(ISERROR(1/COUNTIF(List_2, List_1)), ROW(List_1)-MIN(ROW(List_1))+1), ROWS($A$2:A2)))

Chrisham,

Your array formula is great! My array formula creates unique distinct values, that is perhaps not what he asked for.

Thanks a lot for your contribution!!

Guys,

Realy appreciate your contribution.

I need the unique distinct value.

I'll keep both proposed solution. Always interresting to test and compare :-).

Again, thanks a lot.

I have been looking for something similar to this. I am trying to automate a billing process and need some help with it.