• 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.

Match and Extract Difference in List

Hi sir,
I need a macro to Match comma separated string in list and Extract difference, ie Compare Cell B2 & B3 and B4 & B5,, etc and Extract the difference and paste in other column. Can any one solve my issue..
Note:
Compare in column B2 to end
Sample file attached for your reference.
 

Attachments

Last edited:

Marc L

Excel Ninja
Hi, according to your attachment a VBA demonstration for starters :​
Code:
Sub Demo1()
      Const D = ","
        Dim B, V, R&, W, X, C&
    With Sheet1.[A1].CurrentRegion
        B = .Columns(2)
        ReDim V(2 To UBound(B), 0)
    With Application
        For R = 3 To UBound(B) Step 2
            W = .Trim(Split(B(R - 1, 1), D))
            X = .Match(W, .Trim(Split(B(R, 1), D)), 0)
        For C = 1 To UBound(X)
            If IsNumeric(X(C)) Then W(C) = D
        Next
            V(R, 0) = Join(Filter(W, D, False), D & " ")
        Next
    End With
       .Range("D2:D" & UBound(V)) = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Hi sir,
Thank you..and i need one more function. ie Compare Cells B2,B3,B3 & B4,B5,B6 etc,,based on Vendor Number count.
Sample file attached for your reference. Can You Please resolve it.
 

Attachments

p45cal

Well-Known Member
In the attached is a user defined function called Missing which compares two comma separated lists.
You could use it like this:
=Missing(D2,D3)
78914
where I've named one of the arguments Longer (intended to be the longer of the two lists) and the other Shorter. They don't have to be that way, all the function does is to list what is in the first cell that isn't in the second cell.
To make your comparisons according to vendor you can check for similarity with the likes of:
=IF(B2=B3,Missing(D2,D3),"") which can be copied down. It's up to you which two cells to compare. See attached.
The function is case-insensitive and extra spaces are ignored, although 8X1 is not considered the same as 8 X 1.
See attached.
 

Attachments

p45cal

Well-Known Member
run this which works on the active sheet:
Code:
Sub blah()
lr = Cells(Rows.Count, "B").End(xlUp).Row
With Range("E2:E" & lr)
  .Formula2R1C1 = "=IF(R[-1]C[-3]=RC[-3],Missing(R[-1]C[-1],RC[-1]),"""")"
  .Value = .Value
End With
End Sub
It uses the Missing function. The above code can go in the same code-module as the Missing UDF.
 
Hi sir,
when execute the macro code in module " Run-time Error '438'---- Object doesn't support this property or method Error Shows, Could you
please Resolve that issue?
 
Top