Duplicate records

Topic

New Reply

Greetings,

I receive daily student registration reports in Excel 2010 format. Every now and then I’ll notice the same student is entered multiple times because of the following reasons:

Name: Joe L. Madison vs. Joseph Lance Madison
Address: Main Street vs. Main Str.
Phone #: home # vs. mobile #
E-mail address: JLM@msn.com vs. JLM@aol.com

In the attached sample document, highlighted are records that are same student but entered multiple times (because of reasons mentioned above)
What would be the best way to catch this dups? I was thinking, may be a macro to check combination of birth date + SSN + ID or any other field and if there is a match then flag as “…match found…”

Any ideas?

Regards,

Reply | Quote

Replies

Hi OCM

Perhaps the simplest way (without macros) would be to apply conditional formatting to the two columns where you don’t expect duplicates.
For example, the ID column and the SSN column should contain unique entries.
You could set the background cell colour to red if the count of the values in that column is more than 1 for each ID and SSN value.

zeddy

Reply | Quote

Hi OCM

..or you can use formulas in adjacent columns, as per my file attached.
You can filter the data on the check columns to show the duplicate records, and sort by Name etc to group them together.

zeddy

Reply | Quote

OCM,

Working off your idea, if you take the 3 most unique fields (ID, SS#, DOB) and concatenate them into a string, you will be assured that you can find duplicate students. The code here finds the duplicates by comparing the built strings, rearranges them sequentially, then groups them by color. The fields used for the stings can be easily changed. Doing an alphabetical sort by first names is not reliable because real names and nick names can be very different.

Original list
34365-Students1

After run code
34366-Students2

Code:

Public Sub FindDups()
[COLOR=”#008000″]’DECLARE AND SET VARIABLES[/COLOR]
Dim Str1 As String
Dim Str2 As String
Dim Color
Color = 6
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
[COLOR=”#008000″]’————————————————
‘FIND DUPLICATES[/COLOR]
Application.ScreenUpdating= False
For I = 2 To LastRow  [COLOR=”#008000″]’CYCLE THROUGH ROWS[/COLOR]
    Str1 = Cells(I, 4).Value & Cells(I, 6).Value & Cells(I, 5).Value [COLOR=”#008000″] ‘CREATE BASE STRING[/COLOR]
    For K = I + 1 To LastRow  [COLOR=”#008000″]’CYCLE THROUGH SUCEEDING ROWS[/COLOR]
        Str2 = Cells(K, 4).Value & Cells(K, 6).Value & Cells(K, 5).Value  [COLOR=”#008000″]’CREATE COMPARE STRING[/COLOR]
        If Str1 = Str2 Then [COLOR=”#008000″] ‘IF A MATCH IS FOUND THEN…[/COLOR]
            [COLOR=”#008000″]’MOVE AND COLOR THE MATCHED ROW[/COLOR]
            Rows(I + 1).Insert
            Rows(K + 1).Cut
            Rows(I + 1).Select
            ActiveSheet.Paste
            Rows(K + 1).Delete
            Rows(I & “:” & I + 1).Interior.ColorIndex = Color  [COLOR=”#008000″]’FILL ROW INTERIOR[/COLOR]
            Color = Color + 1
            If Color > 8 Then Color = 6  [COLOR=”#008000″]’RESET THE FILL COLOR[/COLOR]
        End If
    Next K
Next I
Application.ScreenUpdating= True
End Sub

Place the code in a standard module and run the macro from the Developer menu> Macros> FindDups> Run

HTH,
Maud

Reply | Quote