Flagging duplicate values with Countif and Search functions

dsulliva

New Member
Joined
Nov 14, 2009
Messages
1
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
A macro allows more room for development.
This one splits the name into parts and checks for matches where the first 2 parts appear.
Code:
'=============================================================================
'- 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
'-----------------------------------------------------------------------------
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top