Comparing two columns in Excel with exclusion

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

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.

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

You can also do this with Advance Filter Assuming Data in Col A and B In Cell C2 write =SUMPRODUCT(--($B$2:$B$17=A2))=0 Select Data A1:A12 Click on Advanced Filter, Choose copy to another location List Range = $A$1:$A$22 Criteria Range = $C$1:$C$2 (Note C1 to be blank) In Copy to Range choose E1, Say OK This will give you Values of A that don't exist in B

In Cell D2 Write =SUMPRODUCT(--($A$2:$A$22=B2))=0

Select Data B1:B17 Click on Advanced Filter, Choose copy to another location List Range = $A$1:$A$22 Criteria Range = $D$1:$D$2 (Note D1 to be blank) In Copy to Range choose F1, Say OK This will give you Values of B that dont exist in A

You can automate this Via a single line of Code

Sub Get_Data1()

[List1].AdvancedFilter 2, [Crt1], [Dest1], True

End Sub

Sub Get_Data2()

[List2].AdvancedFilter 2, [Crt2], [Dest2], True

End Sub