This ought to do the trick... you will need to play with the columns probably... this assumes source sheet has list A in col A, and list B on Col B of "Sheet1"
It then outputs Only in A in col A, only in B in col B, and what is in both in col C. Output sheet is "Sheet2"
Sub compare()
Dim arr As Variant
Dim varr As Variant
Dim wss As Worksheet
Dim wsd As Worksheet
Dim x, y
Dim match As Boolean
Dim i As Long, j As Long
Application.ScreenUpdating = False
Set wss = Worksheets("Sheet1") 'sheet with source data
Set wsd = Worksheets("Sheet2") 'sheet with output columns
arr = wss.Range("A2:A" & wss.Range("A" & Rows.Count).End(xlUp).Row).Value 'starts at A2 assuming a header
varr = wss.Range("B2:B" & wss.Range("B" & Rows.Count).End(xlUp).Row).Value 'starts at B2 assuming a header
i = 1 'will start the list of values only in A and only in B on row 2 because later we add 1 before writing
j = 1 'will start the list of values that are in both lists on row 2 because later we add 1 before writing
'here we compare lists, write what is only in column A, and what is common
For Each x In arr
match = False
For Each y In varr
If x = y Then 'there is a match in col A vs Col B
match = True
j = j + 1
wsd.Range("C" & j).Value = x 'write the value of X in the list of common values
End If
Next y
If Not match Then
i = i + 1
wsd.Range("A" & i).Value = x 'write the value in the list of items in A but not in B
End If
Next
'now get the list only in B
i = 1
For Each x In varr
match = False
For Each y In arr
If x = y Then match = True
Next y
If Not match Then
i = i + 1
wsd.Range("B" & i).Value = x 'write the value in the list of items in A but not in B
End If
Next
Application.ScreenUpdating = True
End Sub
Bookmarks