Hi
pls see the vbs code to compare 


Private Function COMPARE(Rng1, Rng2 As Range, Op As Integer)
Dim A, B As Variant
Dim Ans1
Dim Test As Boolean
Test = True
If Op = 1 Or Op = 2 Then
    A = Split(Rng1, ","): B = Split(Rng2, ",")
ElseIf Op = 3 Then
    A = Split(Rng2, ","): B = Split(Rng1, ",")
End If
For Each Cl1 In A
    For Each Cl2 In B
        If Cl1 = Cl2 Then
            Ans1 = Ans1 & Cl1 & ","
            Test = False
        End If
    Next Cl2
    
    If Test Then Ans2 = Ans2 & Cl1 & ","
    Test = True
Next Cl1

If Op = 1 Then
    COMPARE = Mid(Ans1, 1, Len(Ans1) - 1)
ElseIf Op = 2 Then
    COMPARE = Mid(Ans2, 1, Len(Ans2) - 1)
ElseIf Op = 3 Then
    COMPARE = Mid(Ans2, 1, Len(Ans2) - 1)
End If

End Function

Syntax
Compare Column A & B Return Unique values from Column A =COMPARE(B4,C4,2)
Compare Column A & B Return Unique values from Column B =COMPARE(B5,C5,3)

Compare Column A & B Return Duplicate Values from both =COMPARE(B6,C6,1)