Flagging duplicate values with Countif and Search functions

[dsulliva]

Hi Everyone...I have a list of names (column A) and in column B I want a formula to flag all duplicate items in column A. I was using the formula =IF(COUNTIF($A$2:$A$10,A2)>1,1,0) where "1" would flag me to a possible duplicte (i.e. I could filter on it).

The data in column A is causing problems. Column A contains names. It could be in the format first name / last name or last name / first name. There also could be a comma or a semi-colon separating the 2. There almost always will be a space between the two. My solution was to use the last 4 or 5 charactes of the column A cell and use that as a condition in the count if. It looks something like this, but I can't get it to work.
=IF(COUNTIF($A$2:$A$13,IF(ISERROR(SEARCH(RIGHT($A2,2),$A2,1))=TRUE,0,1)), 1,0)

Can someone give me some advice? I'm also open to other formulas / functions that would allow me to flag possible duplicate names based upon a partial match of the cell (e.g. a cell in column A containing "Ron Smith" would have to identify "Smith, Ron"; "Smith Ron"; "Smith; Ron"; "Ron, Smith" as a potential duplicate values). Then there's possible duplicate data that contains spelling errors (e.g. Smith vs. Smithh vs. Smyth). I don't think there's any help for that??

I'd prefer not to sort, subtotal, etc. the data and I'm using Excel 2003. I also don't want VBA solutions.

Thanks
Dan

 

[BrianB]

A macro allows more room for development.
This one splits the name into parts and checks for matches where the first 2 parts appear.

'=============================================================================
'- FIND DUPLICATE NAMES (CHECK 2 PARTS OF NAME) IN A LIST (IN COLUMN A)
'- 1. Regular Expression to split name into FirstPart and LastPart (remove garbage)
'- 2. Find FirstPart. If LastPart is there too record a match.
'- To begin with the sheet should only contain a list of names in column A
'- starting in Row 2
'=============================================================================
Dim MyRegExp As Object
Dim MyPattern As String
Dim MyMatches As Variant
Dim MatchCount As Integer
Dim MyMatch As String
'-
Dim ws As Worksheet
Dim FromRow As Long
Dim ToColumn As Integer
Dim LastRow As Long
Dim MyName As String
Dim FirstPart As String
Dim LastPart As String
Dim FoundCell As Range
Dim FoundRow As Long
Dim FoundString As String
'=============================================================================
'- MAIN ROUTINE
'=============================================================================
Sub FIND_DUPLICATES()
    Application.Calculation = xlCalculationManual
    Set ws = ActiveSheet
    ws.Columns("B:I").ClearContents
    ws.Range("B1:I1").Value = Array("Row", "Match", "Row", "Match", "Row", "Match", "Row", "Match")
    LastRow = ws.Range("A65536").End(xlUp).Row
    Set MyRegExp = CreateObject("VbScript.RegExp")
    MyPattern = "\b[a-zA-Z]+\b" ' RE string search pattern
    '-------------------------------------------------------------------------
    '- LOOP NAMES LIST
    For FromRow = 2 To LastRow
        Application.StatusBar = FromRow & "/" & LastRow
        ToColumn = 2
        MyName = ws.Cells(FromRow, 1).Value
        '---------------------------------------------------------------------
        '- REGULAR EXPRESSION TO SPLIT INTO 2 NAMES
        With MyRegExp
            .Global = True
            .ignorecase = True
            .pattern = MyPattern
            Set MyMatches = .Execute(MyName)
        End With
        '---------------------------------------------------------------------
        '- CHECK PARTS
        MatchCount = MyMatches.Count
        FirstPart = MyMatches(0)
        If MatchCount >= 2 Then LastPart = MyMatches(1)
        '---------------------------------------------------------------------
        '- FIND FirstPart MATCHES
        With ws
            Set FoundCell = .Columns(1).Cells.Find(What:=FirstPart, after:=.Cells(FromRow, 1), MatchCase:=False, lookat:=xlPart)
            If Not FoundCell Is Nothing Then
                FirstAddress = FoundCell.Address
                Do
                    '==========================================================
                    '- Part1 MATCH FOUND. CHECK LastPart
                    FoundRow = FoundCell.Row
                    If FoundRow <> FromRow Then     ' exclude FirstPart match
                        FoundString = .Cells(FoundRow, 1).Value
                        If InStr(1, FoundString, LastPart, vbTextCompare) > 0 Then
                            .Cells(FromRow, ToColumn).Value = FoundRow
                            .Cells(FromRow, ToColumn + 1).Value = FoundString
                            ToColumn = ToColumn + 2
                        End If
                    End If
                    '==========================================================
                    Set FoundCell = .Columns(1).Cells.FindNext(FoundCell)
                Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
            End If
        End With
        '----------------------------------------------------------------------
    Next
    '--------------------------------------------------------------------------
    Application.Calculation = xlCalculationManual
    MsgBox ("Done")
    Application.StatusBar = False
End Sub
'-----------------------------------------------------------------------------