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

[alexhihi]

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:

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Column 1	Column 2	Column 3
Wilson, Jack	Ryan, Tommy, John, Chris, Wilson	TRUE
Tommy, Alex	Ryan, Chris	FALSE

<tbody>
</tbody>

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!

 

[JoeMo]

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.

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
	A	B	C
1	Wilson, Jack	Ryan, Tommy, John, Chris, Wilson	TRUE
2	Tommy, Alex	Ryan, Chris	FALSE
3	Jack		#VALUE!
Sheet1

 

[alexhihi]

Thanks Joe! It do works

However, i found that some the name in the cell are separated by "/" instead of ",". e.g.

Column1	Column2	Column3
Wilson, Jack	Ryan, Tommy, John, Chris, Wilson	True
Tommy, Alex	Ryan, Chris	False
Eric/Jack	Eric/John	True

<tbody>
</tbody>

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

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!