Option Explicit
Sub Compare()
Dim LstA As Range, LstB As Range, Cls As Range, Lst As Range
Dim Lst1 As Range, Lst2 As Range, jJ As Byte
[I1].Value = "Times": [k1].Value = "Times"
[H1].Value = [A1].Value: [j1].Value = [b1].Value
Range("d1").Value = "In A not in B": Range("e1").Value = "In B not in A"
Range("f1").Value = "Count of A": Range("g1").Value = "Count of B"
[d1].CurrentRegion.Offset(1).ClearContents
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[H1], Unique:=True
Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[j1], Unique:=True
Set LstA = Range([H1], [h65000].End(xlUp)): Set Lst1 = Range([A1], [a65500].End(xlUp))
Set LstB = Range([j1], [j65000].End(xlUp)): Set Lst2 = Range([b1], [b65500].End(xlUp))
For jJ = 1 To 2
Set Lst = Choose(jJ, LstA, LstB)
For Each Cls In Lst
If Cls.Value <> "" Then
With Cells(2, "E").Offset(, jJ)
.Value = .Value + 1
End With
If Application.CountIf(Choose(jJ, Lst2, Lst1), Cls) > 0 Then _
Cls.Offset(, 1).Value = Application.CountIf(Choose(jJ, Lst2, Lst1), Cls)
If Application.CountIf(Choose(jJ, LstB, LstA), Cls) = 0 Then
Cells(Cells(Rows.Count, 3 + jJ).End(xlUp).Row + 1, 3 + jJ).Value = Cls
End If
End If
Next Cls
Next jJ
Exit Sub
End Sub
Bookmarks