Compare two columns of values

[levoxy]

I have two columns with values that needs to be compared and difference values need to be printed in separate column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]value1[/TD]
[TD]value2[/TD]
[TD]difference[/TD]
[/TR]
[TR]
[TD]apple,curd,milk[/TD]
[TD]orange,apple,water[/TD]
[TD]curd,milk,orange,water[/TD]
[/TR]
[TR]
[TD]pen,paper,bag[/TD]
[TD]bag,paper,belt[/TD]
[TD]pen,belt[/TD]
[/TR]
</tbody>[/TABLE]

please help.

 

[Gerald Higgins]

Re: Please help with compare

Hi, welcome to the board.

Are the individual values ALWAYS separated by commas ?
Are there ALWAYS three values in each input column ? Never more than three ? Never less than three ?

 

[levoxy]

Re: Please help with compare

Yes. it seperated by commas.

No. some have more than 3 values or lesser than 3.

Hi, welcome to the board.

Are the individual values ALWAYS separated by commas ?
Are there ALWAYS three values in each input column ? Never more than three ? Never less than three ?

 

[Gerald Higgins]

Re: Please help with compare

Hi, there is no need to quote my post

What is the maximum possible number of values in a single entry ?

 

[levoxy]

Re: Please help with compare

max is 27.

 

[Gerald Higgins]

Re: Please help with compare

Do you only need to do this task once, or is it a frequent task ?

 

[YasserKhalil]

Re: Please help with compare

Hello
Try this code

Sub Test_CompareTwoArrays_UDF()
    Dim arr     As Variant
    Dim a       As Variant
    Dim b       As Variant
    Dim i       As Long


    arr = Range("A1").CurrentRegion.Value
    For i = LBound(arr, 1) + 1 To UBound(arr, 1)
        a = Split(arr(i, 1), ",")
        b = Split(arr(i, 2), ",")
        arr(i, 3) = Join(CompareTwoArrays(a, b), ",")
        Erase a: Erase b
    Next i
    Range("A1").CurrentRegion.Value = arr
End Sub


Function CompareTwoArrays(v1 As Variant, v2 As Variant)
    Dim v       As Variant
    Dim i       As Long
    Dim t       As Long
    Dim p       As Long


    On Error Resume Next
        ReDim v(0 To UBound(v1))
        p = 0
        For i = LBound(v1) To UBound(v1)
            t = Application.Match(v1(i), v2, 0)
            If t = 0 Then
                v(p) = v1(i)
                p = p + 1
            Else
                t = 0
            End If
        Next i
    
        ReDim Preserve v(1 To p + UBound(v2))
        p = p + 1
        For i = LBound(v2) To UBound(v2)
            t = Application.Match(v2(i), v1, 0)
            If t = 0 Then
                v(p) = v2(i)
                p = p + 1
            Else
                t = 0
            End If
        Next i
    On Error GoTo 0
    
    CompareTwoArrays = v
End Function

 

[levoxy]

Re: Please help with compare

it will be frequent.

 

[levoxy]

Re: Please help with compare

Hello, I could not get the code to work, I tried by running the vba from the cell E1 by {=CompareTwoArrays(A1,C1)}?

 

[YasserKhalil]

Re: Please help with compare

Press Alt + F11 >> From Insert click Module
Copy the code to the module inserted
Go back to the worksheet ( I supposed there are three columns and the results would be put in the third column)
Run the macro named "Test_CompareTwoArrays_UDF" by pressing Alt + F8 and select the macro name and click 'Run'