Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Comparing two columns in Excel with exclusion

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.

like image 314
Ashish Gupta Avatar asked Apr 24 '11 01:04

Ashish Gupta


1 Answers

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

like image 64
Alex P Avatar answered Oct 15 '22 07:10

Alex P