Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate records

    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,
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts

    An alternate approach using VBA

    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
    Students1.png

    After run code
    Students2.png

    Code:
    Public Sub FindDups()
    'DECLARE AND SET VARIABLES
    Dim Str1 As String
    Dim Str2 As String
    Dim Color
    Color = 6
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    '------------------------------------------------
    'FIND DUPLICATES
    Application.ScreenUpdating= False
    For I = 2 To LastRow  'CYCLE THROUGH ROWS
        Str1 = Cells(I, 4).Value & Cells(I, 6).Value & Cells(I, 5).Value  'CREATE BASE STRING
        For K = I + 1 To LastRow  'CYCLE THROUGH SUCEEDING ROWS
            Str2 = Cells(K, 4).Value & Cells(K, 6).Value & Cells(K, 5).Value  'CREATE COMPARE STRING
            If Str1 = Str2 Then  'IF A MATCH IS FOUND THEN...
                'MOVE AND COLOR THE MATCHED ROW
                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  'FILL ROW INTERIOR
                Color = Color + 1
                If Color > 8 Then Color = 6  'RESET THE FILL 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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-12 at 22:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •