Match and Extract Difference in List

[jeyachandran]

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.

 

[Marc L]

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

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 !​

​

 

[jeyachandran]

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.

 

[p45cal]

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)

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.

 

[jeyachandran]

Hi sir,
Thanks for your support. UDF is working fine but sometimes hangs, So could you please create macro with one button click.

 

[p45cal]

run this which works on the active sheet:

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.

 

[jeyachandran]

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?

 

[p45cal]

What line?
What version of excel?
try replacing
Formula2R1C1
with
FormulaR1C1

 

[jeyachandran]

Excellent sir..now working fine...Really thanks for your support..