• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to compare two columns data and extract non matching Data

jeyachandran

New Member
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.
 

Attachments

Marc L

Excel Ninja
Hi, according to your attachment a beginner VBA demonstration for starters :​
Code:
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 !​
 

Marc L

Excel Ninja
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 :​
Code:
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 !​
 
Top