Microsoft Excel - Deleting whole rows base on duplicate entry in one column

I am trying to delete duplicate records in an excel file. I've concatenated the three rows I want to base my query on into row C. I've tried several different functions to highlight the duplicate records, but once I do that and then sort by those results I am not able to highlight only the duplicates and delete.

I know I'm doing something wrong, but cannot figure it out.

Does someone have an easy solution to do this?

Again, I need to compare all rows for duplicates based on column C. All duplicates need to be highlighted or marked so that I can sort by that marking and delete them.

Thanks in advance for any help!!!!

Try this on your sample workbook. Example

1. Select the range A4:B26
2. From menubar: choose Data > Filter > Advanced Filter (dialog box will open)
3. Check the radio button for "Copy to another location"
3. Check "unique records only"
4. Click once in the "Copy to" field of the dialog box, then click in cell I4 (next to your current "results" list)
5. Click the OK button
 this will also help you go through this articel:

Rules for Advanced Filtering

1) You list or table must have headings at row 1 of the table.
2) Your data must be laid out directly underneath their appropriate headings.
3) When using criteria the cell(s) directly above must have exact copies of the table heading(s). See picture below;
4) When using Excel formula criteria the cell(s) directly above must NOT have exact copies of the table heading(s). They should be blank, or have a heading that is NOT the same as your table heading(s).
5) If using the "Copy to another location" option you can only copy to the same Worksheet as your original table is located. To copy to another Worksheet you can Start by going to Data>Advanced Filter while on the Worksheet your data should be copied to.
6) To create an OR condition place each criteria in a new cell directly below each other, vertically.
7) To create an AND condition place criteria in a new cell directly next each other running left top right, horizontally

http://www.ozgrid.com/forum/showthread.php?t=93558

http://www.ozgrid.com/Excel/advanced-filter.htm

Sub RemoveDupe()
    Dim rCell As  Range
    Dim rRange As Range
    Dim lCount As Long
    
    Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
    lCount = rRange.Rows.Count
    
    For lCount = lCount To 1 Step -1
        With rRange.Cells(lCount, 1)
            If WorksheetFunction. CountIf(rRange, .Value) > 1 Then
                .EntireRow.Delete
            End If
        End With
    Next lCount
End Sub

Thanks for the replies -- I've tried the Advanced filter, but because not all of the data in each column of the row is a duplicate to another row I havn't been able to get it to work.

In regards to your second post, is this something that I put in the macro for the sheet? I'm just learning excel so sorry for any obvious questions.

The following sub is one that I wrote a while back that tries to "intelligently" remove duplicates from the current column.  This method uses flagging and sorting so that it can delete all the duplicate rows at once, rather than looping and deleting individual rows (this is MUCH faster).  I have this sub in a module that is loaded on startup and I have a custom button in my Excel toolbar that lets me just click and kill.

Sub KillDups()
    Dim screenUpdates As Boolean
    Dim calcMode As Integer
    Dim rng As Range
    Dim intCol As Integer
    Dim lngTop As Long
    Dim lngBottom As Long
    ' save settings
    screenUpdates = Application.ScreenUpdating
    calcMode = Application.Calculation
    ' turn off updates and calcs
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    'keep track of the currently selected column
    intCol = ActiveCell.Column
    'find the header of the current column (first row)
    If ActiveCell.Row > 1 Then
        If Len(ActiveCell.Offset(-1, 0).Formula) > 0 Then
            lngTop = ActiveCell.End(xlUp).Row
        Else
            lngTop = ActiveCell.Row
        End If
    Else
        lngTop = 1
    End If
    'find the last row of the current data
    lngBottom = ActiveCell.End(xlDown).Row
    'insert a temporary column
    Columns(intCol + 1).Insert
    'point to the formula range for finding dups
    Set rng = Range(Cells(lngTop + 1, intCol + 1), _
                    Cells(lngBottom, intCol + 1))
    'sort by the column selected
    Range(lngTop & ":" & lngBottom).Sort Key1:=Cells(lngTop + 1, intCol), _
                                         Order1:=xlAscending, _
                                         Header:=xlYes
    'set heading and formula values for finding dups; convert to values
    rng.Cells(1, 1).Offset(-1, 0) = "Dup"
    rng.FormulaR1C1 = "=IF(R[-1]C[-1]=RC[-1], 1, 0)"
    rng.Calculate
    rng = rng.Value
    'sort by duplicate status
    Range(lngTop & ":" & lngBottom).Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
    'find the first duplicate and delete from there to the bottom
    Set rng = rng.Find(What:="1", LookIn:=xlValues, LookAt:=xlPart)
    If Not rng Is Nothing Then
        Range(rng, Cells(lngBottom, intCol + 1)).EntireRow.Delete
    End If
    'remove the temporary column and reset the usedrange
    Columns(intCol + 1).Delete
    ActiveSheet.UsedRange
    ' clean up
    Set rng = Nothing
    ' turn off updates and calcs
    Application.ScreenUpdating = screenUpdates
    Application.Calculation = calcMode
End Sub

Good luck,

Brendon

Hi,

Paste this code into query analyser and see if it does what you need

Please refer http://bytes.com/forum/thread771160.html for more details.

-Paresh

Hi,

You can also delete by writing SQL Query like below:

delete bad_rows.*
from test as bad_rows
   inner join (
      select day, MIN(id) as min_id
      from test
      group by day
      having count(*) > 1
   ) as good_rows on good_rows.day = bad_rows.day
      and good_rows.min_id <> bad_rows.id;

Please refer http://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/ for more details.

-Paresh

Hi,

you can use following code from http://www.ozgrid.com/forum/showthread.php?t=93558

Sub RemoveDupe()
    Dim rCell As  Range
    Dim rRange As Range
    Dim lCount As Long
    
    Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
    lCount = rRange.Rows.Count
    
    For lCount = lCount To 1 Step -1
        With rRange.Cells(lCount, 1)
            If WorksheetFunction. CountIf(rRange, .Value) > 1 Then
                .EntireRow.Delete
            End If
        End With
    Next lCount
End Sub

Regards,

Paresh