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)
Bookmarks