Macro to compare two columns data and extract non matching Data

[jeyachandran]

Dear sir,
I need a macro to compare two column values (B & C) and extract missing values and to be populate in column C. the macro to be compare each string from
column C to B and extract the result in column D

Could you help me to resolve my issue?


Note: Example missing values shown in column F for your reference.
* compare each text and numbers and alpha numeric numbers
* compare Special Characters like # @ , : ; /. ( ) _ " ' etc also
* compare from 2 to last row.

 

[Marc L]

Hi, according to your attachment a beginner VBA demonstration for starters :​

Sub Demo1()
        Dim V, R&, W, C%
    With Range("A2:A" & [A1].CurrentRegion.Rows.Count).Columns
            V = .Item("B:C").Value2
        For R = 1 To UBound(V)
        For Each W In [{"&","(",")","*",",","-","/",":"}]
            V(R, 1) = Replace(V(R, 1), W, " ")
            V(R, 2) = Replace(V(R, 2), W, " ")
        Next
            V(R, 1) = Split(Application.Trim(V(R, 1)))
            V(R, 2) = Split(Application.Trim(V(R, 2)))
        For C = UBound(V(R, 1)) To 0 Step -1
            If IsNumeric(Application.Match(V(R, 1)(C), V(R, 2), 0)) Then V(R, 1)(C) = False _
             Else If Application.Match(V(R, 1)(C), V(R, 1), 0) <= C Then V(R, 1)(C) = False
        Next
            V(R, 1) = Join(Filter(V(R, 1), False, False), ", ")
        Next
           .Item(4).Value2 = V
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[jeyachandran]

Excellent job..thank you so much...

 

[jeyachandran]

Dear Marc L,
I need some small changes.
* This code compare text character only but i need numeric and alpha numeric characters also..
I attached file for your reference..

Thank you
Jeyachandran

 

[Marc L]

As the VBA procedure does not make any difference between letters and numbers,​

in fact just taking a glance to the result you should - must ! - notice there is some 'forbidden' characters​

which were not in your initial attachment : a semi colon - like LEG7564; - and the Line Feed character (Ascii #10) …​

​

As a beginner level VBA procedure - no time for an expert way - you have to add the missing characters to delete within the code​

like in this revamped procedure in the variable D where I use the vbLf constant for the Line Feed and​

you could also use the Chr statement (VBA help is your best friend !) for any character which is not on your keyboard :​

Sub Demo1r()
        Dim D, V, R&, W, C%
            D = Split(vbLf & " & ( ) * , - / : ;")
    With Range("A2:A" & [A1].CurrentRegion.Rows.Count).Columns
            V = .Item("B:C").Value2
        For R = 1 To UBound(V)
        For Each W In D
            V(R, 1) = Replace(V(R, 1), W, " ")
            V(R, 2) = Replace(V(R, 2), W, " ")
        Next
            V(R, 1) = Split(Application.Trim(V(R, 1)))
            V(R, 2) = Split(Application.Trim(V(R, 2)))
        For C = UBound(V(R, 1)) To 0 Step -1
            If IsNumeric(Application.Match(V(R, 1)(C), V(R, 2), 0)) Then V(R, 1)(C) = False _
             Else If Application.Match(V(R, 1)(C), V(R, 1), 0) <= C Then V(R, 1)(C) = False
        Next
            V(R, 1) = Join(Filter(V(R, 1), False, False), ", ")
        Next
           .Item(4).Value2 = V
    End With
End Sub

You may Like it !​

​

 

[jeyachandran]

Thank you so much Sir...