Search Duplicate Reference and Return Results

[simonstaton]

Hi,

I have a .csv file with all our products references, alternative references and Names

I want it to scan over all "references" and "alternative references" and return the duplicates so I can go back and fix them.

the csv file looks like so:

Ref:----Alt Ref:----Name:
10------15; 17----8ft Tramp Pads
9-------10; 11----8ft Tramp Bed

as you can see the alt ref of the bed has the same ref as the Pads.

Thanks,
Simon

 

[simonstaton]

any ideas?

 

[Jerry Sullivan]

Hi Simon,
It looks like you just need to find all rows that have a value in the Ref field that appears anywhere in the Alt Ref Field that also appears in the other Field and visa versa Does that sound right?

It's difficult to interpret your description of the .csv data (do the dashes represent spaces or dashes, where are the commas?).

Let's assume the first step of your process will be to import or open the CSV file and save as an Excel file. What will the format of the values in Columns A and B be?

P.S. You might want to wait a little longer before bumping up your post if you haven't received a reply. I typically look for posts that have no replies after 24 hours, and your early bump took that off my radar.

 

[simonstaton]

Yes thats correct. The dashes represent spaces and it can also be an excel file.

 

[Jerry Sullivan]

If I'm understanding your example, the Ref Column (A) will have only one value, the Alt Ref Column (B) could have multiple values
separated by semicolons.

The code below should get you started. Not sure what you want to do to mark the rows with matching values.
In this example, when an Alt Ref. Value is found that matches a Ref, text is entered in Column D of that Row
that reads "Matches: " followed by the matching Alt Refs. (could be more than one per row).

Sub Identify_Duplicates()
    Dim arrRefs As Variant
    Dim c As Range
    Application.ScreenUpdating = False
    With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        arrRefs = .Cells
        For Each c In .Offset(0, 1)
            c.Offset(0, 2) = AltMatchesRef(c.Value, arrRefs)
        Next
    End With
    Erase arrRefs
End Sub
 
Private Function AltMatchesRef(strAltRefs As String, _
        ByVal arrRefs As Variant) As String
    Dim strList() As String, strCurr As String, strReturn As String
    Dim i As Long, j As Long
    strList = Split(strAltRefs, ";")
    For i = LBound(strList) To UBound(strList)
        strCurr = Trim(strList(i))
        For j = LBound(arrRefs) To UBound(arrRefs)
            If arrRefs(j, 1) = strCurr Then
                If strReturn = vbNullString Then
                    strReturn = "Matches: " & strCurr
                Else
                    strReturn = strReturn & "; " & strCurr
                End If
                Exit For
            End If
        Next j
    Next i
    AltMatchesRef = strReturn
End Function