[Help] Compare multiple values in 2 separate cells to find duplicate value

alexhihi

New Member
Joined
Nov 4, 2018
Messages
2
Hello all,

I am newbie in this forum and excel VB. I have a hard time to find duplicate value when comparing multiple values in 2 separate cells. As there are many record i'll need to compare, so would like seek help here and solve the problem by VB. Example as below:[TABLE="width: 582"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]Wilson, Jack[/TD]
[TD]Ryan, Tommy, John, Chris, Wilson[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Tommy, Alex[/TD]
[TD]Ryan, Chris[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

To compare the names (each name will be separate by comma) between column 1 and 2, if there is duplicate value (e.g. Wilson), will return "TRUE" in column 3.


Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the forum!

Here's a user-defined function (UDF) you can try after you install it. Use like any worksheet function (see example below the function).
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Function FindDup(R As Range) As Boolean
Dim Vin As Variant, Vcompare As Variant, i As Long, j As Long
If IsEmpty(R(2)) Then
    FindDup = CVErr(xlErrValue)
    Exit Function
End If
Vin = Split(R(1), ",")
Vcompare = Split(R(2), ",")
For i = LBound(Vin) To UBound(Vin)
    For j = LBound(Vcompare) To UBound(Vcompare)
        If Trim(Vcompare(j)) = Trim(Vin(i)) Then
            FindDup = True
            Exit Function
        End If
    Next j
Next i
FindDup = False
End Function
Excel Workbook
ABC
1Wilson, JackRyan, Tommy, John, Chris, WilsonTRUE
2Tommy, AlexRyan, ChrisFALSE
3Jack#VALUE!
Sheet1
 
Upvote 0
Thanks Joe! It do works :)

However, i found that some the name in the cell are separated by "/" instead of ",". e.g.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]Wilson, Jack[/TD]
[TD]Ryan, Tommy, John, Chris, Wilson[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]Tommy, Alex[/TD]
[TD]Ryan, Chris[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]Eric/Jack[/TD]
[TD]Eric/John[/TD]
[TD]True[/TD]
[/TR]
</tbody>[/TABLE]



I tried modified the code and the result is negative. Would you please comment the area i should amend?

Code:
Function FindDup(R As Range) As Boolean
Dim Vin As Variant, Vcompare As Variant, i As Long, j As Long, p As Long, k As Long, Vin2 As Variant, Vcompare2 As Variant, Find1 As Boolean, Find2 As Boolean
If IsEmpty(R(2)) Then
    FindDup = CVErr(xlErrValue)
    Exit Function
End If
Vin = Split(R(1), ",")
Vin2 = Split(R(1), "/")
Vcompare = Split(R(2), ",")
Vcompare2 = Split(R(2), "/")
For i = LBound(Vin) To UBound(Vin)
    For j = LBound(Vcompare) To UBound(Vcompare)
        If Trim(Vcompare(j)) = Trim(Vin(i)) Then
            Find1 = True
            Exit Function
        End If
    Next j
Next i
Find1 = False
For p = LBound(Vin2) To UBound(Vin2)
    For k = LBound(Vcompare2) To UBound(Vcompare2)
        If Trim(Vcompare2(k)) = Trim(Vin2(p)) Then
            Find2 = True
            Exit Function
        End If
    Next k
Next p
Find2 = False


If Find1 = True Then
FindDup = Find1
ElseIf Find2 = True Then
FindDup = Find2
Else: FindDup = False
End If


End Function

Thanks!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top