none
Avoiding duplicates but over more than one column?

    Question

  • I looked at the 3 other messages that came up when I put in my title here but I can't make heads or tails of the responses.  My challenge is that we can have duplicate entries across about 3 columns.

    I hope I can explain.  Column C is for the first name, Column D is for the last name, Column F is for the company.  My problem is that the names are not always 100% identical even though there can be duplicates of the same person, i.e., when in one instance someone will use Jim and in another will use James.  Also, the rest of the columns contain contact information so ideally, any process would highlight the duplicates but wouldn't delete them so that the user could verify which row(s) of data to keep and which not to keep.

    Is it possible to come up with a means to show up duplicates?  Data validation and conditional formatting, both things that came up in a web search, I couldn't get to work as I'm dealing with more than one column.  With one column, dealing wtih duplicates is easy, I've done it before.  But how to handle finding similar entries?  Thank you so much for anyone who can help!  This is something that our salaries depend on as its our two sales lists.  And I have very intolerant people I work with that don't understand how hard it is to visually keep on top of hundreds of entries in a spreadsheet keeping an eye out for duplicates. Thank you!


    • Edited by SGFan Tuesday, July 09, 2013 6:05 PM wording
    Tuesday, July 09, 2013 6:05 PM

Answers

  • You could sort your data based on last name, then step down the column to find the duplicates and decide what to do with each.

    Or, you can use a macro - select the cell with the header in column D, and run this macro. It will show the first set of duplicated last names. Then, when you have decided what to do, select the lowest currently visible last name and re-run the macro to show the next, and so on until when you run the macro the filters are not applied.

    Sub FindDuplicatedLastNames()
        Dim rngC As Range
        Dim rngS As Range
        Set rngS = ActiveCell(2)
        If ActiveSheet.FilterMode Then ActiveSheet.Cells.AutoFilter
        For Each rngC In Range(rngS, Cells(Rows.Count, "D").End(xlUp))
            If Application.CountIf(Range(rngS, rngC), rngC) = 1 And Application.CountIf(Range("D:D"), rngC) > 1 Then
                Range("D:D").AutoFilter field:=1, Criteria1:=rngC.Value
                Exit Sub
            End If
        Next rngC
    End Sub



    Tuesday, July 09, 2013 6:52 PM
  • Before running the macro, take off all filters. Sort your table based on column D, and then select the heading of column D, and run the macro - if any cell outside of column D is selected the macro will not work properly. It should show the first value in column D that has duplicated entries. Then select the last visible cell when you are done, and run the macro again.

    An alternative way is to insert a column of formulas. Sort based on D, and then in a blank column, insert a formula like

    =COUNTIF(D:D,D2)

    and copy down. Then filter on that column, showing values greater than 1.

    Bernie

    Wednesday, July 10, 2013 3:36 PM

All replies

  • IMO, this is one of the best and my most  used code ever by RunDebruin

    Sub FindDupeBlue()
            'WOP here
        Dim FirstRow As Long
        Dim LastRow As Long
        Dim LRow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
            'We select the sheet so we can change the window view
            .Select
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
            'Set the first and last row to loop through
            FirstRow = .UsedRange.Cells(2).Row
            LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
            'We loop from Lastrow to Firstrow (bottom to top)
            For LRow = LastRow To FirstRow Step -1
                'Check the values in column A
                If Not IsError(.Cells(LRow, "G").Value) Then
                    If .Cells(LRow, "C").Value = .Cells(LRow + 1, "C").Value And _   'logic Name is the same + 
                       .Cells(LRow, "D").Value = .Cells(LRow + 1, "D").Value And _
                       .Cells(LRow, "F").Value = .Cells(LRow + 1, "F").Value then
                       

                        .Cells(LRow + 1, "I").Cells.Interior.ColorIndex = 33



                    End If
                        'Do While .Cells(LRow - 1, "I").Value = .Cells(LRow, "I").Value
                            '.Cells(LRow, "N").Value = .Cells(LRow + 1, "N").Value
                            '.Cells(LRow, "N").Cells.Interior.ColorIndex = 16
                            'LRow = LRow - 1
                            'If LRow = FirstRow Then Exit Do
                        'Loop
                    
                End If
            Next LRow
        End With
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    End Sub


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Tuesday, July 09, 2013 6:46 PM
  • You could sort your data based on last name, then step down the column to find the duplicates and decide what to do with each.

    Or, you can use a macro - select the cell with the header in column D, and run this macro. It will show the first set of duplicated last names. Then, when you have decided what to do, select the lowest currently visible last name and re-run the macro to show the next, and so on until when you run the macro the filters are not applied.

    Sub FindDuplicatedLastNames()
        Dim rngC As Range
        Dim rngS As Range
        Set rngS = ActiveCell(2)
        If ActiveSheet.FilterMode Then ActiveSheet.Cells.AutoFilter
        For Each rngC In Range(rngS, Cells(Rows.Count, "D").End(xlUp))
            If Application.CountIf(Range(rngS, rngC), rngC) = 1 And Application.CountIf(Range("D:D"), rngC) > 1 Then
                Range("D:D").AutoFilter field:=1, Criteria1:=rngC.Value
                Exit Sub
            End If
        Next rngC
    End Sub



    Tuesday, July 09, 2013 6:52 PM
  • IMO, this is one of the best and my most  used code ever by RunDebruin

    Sub FindDupeBlue()

    [snip]

    Thank you for your reply!

    Well, this one gave a really funny result, I must admit.  Cell I833 remains blank but I834 to I845 (2 cells below print area included! got painted blue, of the empty rows! <g>

    Something horribly wrong went on there.  I'll try to see if I can figure out what but I don't read VB code very well.

    I'll report back.

    Wednesday, July 10, 2013 3:16 PM
  • You could sort your data based on last name, then step down the column to find the duplicates and decide what to do with each.

    Or, you can use a macro - select the cell with the header in column D, and run this macro. It will show the first set of duplicated last names. Then, when you have decided what to do, select the lowest currently visible last name and re-run the macro to show the next, and so on until when you run the macro the filters are not applied.

    Sub FindDuplicatedLastNames()
        Dim rngC As Range
        Dim rngS As Range
        Set rngS = ActiveCell(2)
        If ActiveSheet.FilterMode Then ActiveSheet.Cells.AutoFilter
        For Each rngC In Range(rngS, Cells(Rows.Count, "D").End(xlUp))
            If Application.CountIf(Range(rngS, rngC), rngC) = 1 And Application.CountIf(Range("D:D"), rngC) > 1 Then
                Range("D:D").AutoFilter field:=1, Criteria1:=rngC.Value
                Exit Sub
            End If
        Next rngC
    End Sub

    Thanks for your reply!

    This actually looked promising.  I realized that after trying the one above that coloured cells blue, that colouring wasn't the best approach.  Like the conditional formatting option available to show dupes, our sheets already have various colouring protocols in them and colouring any  duplicates just makes the sheet a bit harder to work with.

    Doing something to filter would be better since doing this manually has been a pain.  That's actually what I have to do re your advice to sort on last name.  That's been my approach - sort by last name and then manually and by eye run down the entire LOOOONNNNGGG sheet to try to find dupes.  Anything to help automate any of that, but - and now I know this is best, _without_ applying colours - would be ideal.

    But the above didn't give viable results so I need to give you more info I think to resolve.

    The sheet ended up getting filtered entirely so that no rows showed after applying this macro.  So don't know what caused that.  However, I have several filter buttons on this sheet from columns B to F:

    B= Caller (employee assigned)

    C= First Name

    D= Last Name

    E= Title

    F= Company

    would these filters make the macro not behave ideally?

    Thx!






    • Edited by SGFan Wednesday, July 10, 2013 3:26 PM wording
    Wednesday, July 10, 2013 3:23 PM
  • Before running the macro, take off all filters. Sort your table based on column D, and then select the heading of column D, and run the macro - if any cell outside of column D is selected the macro will not work properly. It should show the first value in column D that has duplicated entries. Then select the last visible cell when you are done, and run the macro again.

    An alternative way is to insert a column of formulas. Sort based on D, and then in a blank column, insert a formula like

    =COUNTIF(D:D,D2)

    and copy down. Then filter on that column, showing values greater than 1.

    Bernie

    Wednesday, July 10, 2013 3:36 PM