Comparing two columns in Excel with exclusion

excel excel-2007 worksheet-function
Ashish Gupta picture Ashish Gupta · Apr 24, 2011 · Viewed 15.8k times · Source

I want to compare values in two columns in Excel as depicted in the image below :- enter image description here

Using the formula, I want to put the values in the "Values of A which don't exist in B" and "Values of B which don't exist in A". Any help is appreciated.

I have shared the same excel sheet here.

Answer

Alex P picture Alex P · Apr 24, 2011

The following will work - for each, add the formula in row 2 and then drag down

Values of A that do not exist in B

=IF(ISERROR(MATCH($A$2:$A$20,$B$2:$B$17,0)),A2,"")

Result = x, y, z, i, j ,k, l, u

NB: Your example spreadsheet is incorrect as u is in Col A but not Col B but you do not list it in your result set in Col C

Values of B that do not exist in A

=IF(ISERROR(MATCH($B$2:$B$17,$A$2:$A$20,0)),B2,"")

Result = q, r, e, f, g

Related questions

Compare data of two Excel Columns A & B, and show data of Column A that do not exist in B

I have an Excel file that has columns A and B, both have data that may or may not exist in other column, I'm only interested in the data of column A that do not exist in B. These Columns …

Read More
How do I count not equal to wildcard characters in Excel?

Using COUNTIF(range,"<>") can I count non-empty cells and with COUNTIF(range,"~?") or COUNTIF(range,"~*") can I calculate the number of cells matching a wildcard character, but can I do it with one COUNTIF() ? COUNTIF(range,"<>~?") …

Read More
Excel - Recursive VLookup

I recently came to know about Ctrl+Shift+Enter array formulas of Excel and currently still learning them. Coming to my problem, SheetA: Product Code S1 19875 S2 19834 S1 13575 S1 35675 S2 47875 SheetB: Code Indent 19875 40 19834 15 13575 22 35675 25 47875 20 I need to do Sum of all …

Read More