Comparing two columns in Excel with exception

Question

I want to compare the values ​​in two columns in Excel as shown in the image below:

Using the formula, I want to put the values ​​in "Values ​​of A that do not exist in B" and "Values ​​of B that do not exist in A". Any help is appreciated.

I have shared the same excel sheet here .

Answer 1

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

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 sample table is incorrect, since u is in Col A but not Col B, but you did 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

Answer 2

You can also do this with the 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 Advanced Filter, select Copy to another location. List Range = $ A $ 1: $ A $ 22 Criteria Range = $ C $ 1: $ C $ 2 (note C1 must be empty) In the "Copy to range" field, select "E1", "Say" OK "This will give you the values ​​A, which are not in B

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

Select data B1: B17 Click Advanced Filter, select Copy to another location. List Range = $ A $ 1: $ A $ 22 Range of criteria = $ D $ 1: $ D $ 2 (note D1 must be empty) In the "Copy to range" field, select "F1", say "OK" This will give you B values ​​that do not exist in A

You can automate this through 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