Compare 2 columns and return differences

[zinc]

Hi All,

Thanks in advance on any help.

How do i have 4 columns on 1 worksheet that contained:
Col A (list A)
Col B (list B)
Col C (compare A,B and return found in Col A but not Col B)
Col D (compare A,B and return found in Col B but not Col A)

Example:
Col A (list A)
111
555
333

Col B (list B)
222
555
444

Result:
Col C
111
333

Col D
222
444

 

[JackDanIce]

Welcome to the board

I would use VLOOKUP or MATCH to compare all the items in column A against column B and whenever it does not return a value, that item is unique to column A. Switch the columns around and you can find the unique items in column B

In Column C:

=IF(MATCH(A2,B:B,0),"",A2)

And drag to the last row of column A. Any row that has a blank value means it is not unique to column A. Any row with a value is unique to column A

In Column D:

=IF(MATCH(B2,A:A,0),"",B2)

And drag to the last row of column B. Any row that has a blank value means it is not unique to column B. Any row with a value is unique to column B

 

[zinc]

Excel Workbook
	A	B	C	D
1	List	List	Result	Result
2	111	222	111	222
3	555	555	333	444
4	333	444
Result on Excel Sheet1

 

[zinc]

Gratitude for the welcome Jack and the prompt response. =)
is there any way i can run this via a recorded macro so i can skip the Vlookup?

 

[zinc]

Expected result from your formula, possible to return a result as in post#3 as i need to copy the differences to another worksheet.

Excel Workbook
	A	B	C	D
1	List	List	Result	Result
2	111	222	#N/A	#N/A
3	555	555
4	333	444	#N/A	#N/A
Sheet1

 

[JackDanIce]

Sorry, formulae should be:

=IF((ISERROR(MATCH(A2,B:B,0))),A2,"")

and

=IF((ISERROR(MATCH(B2,A:A,0))),B2,"")

Then select columns C and D and copy and paste over with the values. Then you can sort them into order to remove the blank cells.

Macro version is:

Sub UniqueValues()
Application.ScreenUpdating = False
Dim i As Long
i = 2
Do While Not IsEmpty(Cells(i, 1))
    Cells(i, 3).Formula = "=IF((ISERROR(MATCH(A" & i & ",B:B,0))),A" & i & ","""")"
    i = i + 1
Loop
i = 2
Do While Not IsEmpty(Cells(i, 2))
    Cells(i, 4).Formula = "=IF((ISERROR(MATCH(B" & i & ",A:A,0))),B" & i & ","""")"
    i = i + 1
Loop
With Range("A1")
    .CurrentRegion.Copy
    .PasteSpecial Paste:=xlPasteValues
End With
Range("C1") = "Unique in A"
i = Range("C" & Rows.Count).End(xlUp).Row
Range("C2:C" & i).Sort key1:=Range("C1"), order1:=xlAscending
Range("D1") = "Unique in B"
i = Range("D" & Rows.Count).End(xlUp).Row
Range("D2:D" & i).Sort key1:=Range("D1"), order1:=xlAscending
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[zinc]

Thank you very very much, although i am very new here but the help and response from you has been really amazing. You are a nice guy Jack. Gratitude!!!