Removing duplicates

[PaulioH]

Hi,

I currently have a spreadsheet where I have duplicate records in some of the rows. Is there an easy way that I can have only 1 of each record in the spreadsheet without having to delete them manually?

I have recently been using the AutoFilter to do this, but it's still very time consuming when you have thousands of records to deal with!!

Thanks,
Paul

 

[al_b_cnu]

Hi Paul,

One way would be to use advanced filter - tick the 'Unique Records' box & copy to another location

 

[Peter_SSs]

PaulioH

Is your data like List 1 where there is just a single column to determine duplicates?

Or like List 2 where multiple columns need to be checked to determine if the row is a duplicate?

Mr Excel.xls
	A	B	C	D	E	F
1	List 1			List 2
2	Fred			Fred	Smith
3	Bill			Bill	Jones
4	Fred	Duplicate		Fred	Black
5	Sam			Fred	Smith	Duplicate
6	Sam	Duplicate		Sam	Miles
7	Fred	Duplicate		Sam	Miles	Duplicate
8	Bob			Bill	Smith
Delete Duplicates

 

[PaulioH]

Yes, it is just a single column that is determining the duplicates.

 

[Peter_SSs]

PaulioH

Alan's method should work fine.

However, if you want an alternative to consider, here is one. You mentioned that using AutoFilter for this task was time-consuming with lots of records. Here is a fairly quick way using AutoFilter.

1. Assuming your list is in column A starting in row 2, in B2 put =COUNTIF(A$1:A2,A2)=1
2. Copy this down the whole length of your data. If your data has no gaps (blank rows) this can be done very quickly by selecting cell B2 and double-clicking the 'fill handle' (little black square at the bottom right of the cell).
3. Activate AutoFilter
4. Filter column B to FALSE
5. Select all (visible) rows. This can be done quickly by selecting cell B2, hold SHIFT and double-click the bottom border of cell B2
6. Right click in the selected area and choose 'Delete Row'
7. When the deletion has happened, remove AutoFilter altogether.
8. Delete column B

Shouldn't take very long and you will be left with a list of unique values.

 

[Zack Barresse]

While I belive Alan has provided the fastest solution for you, also take a look at brettdj's Duplicate Master ...

http://members.iinet.net.au/~brettdj/

 

[dantb]

The below code looks for duplicates in column "A" if this might help, thanks to some help last week. Hope this helps, Dan

Sub myDeleteDupRows()
Application.ScreenUpdating = False
Dim myLastRow As Long
Dim i As Long
' Capture last row
myLastRow = Range("A65536").End(xlUp).Row
' Insert temporary work column
Columns("B").Insert Shift:=xlToRight
' Insert count formula
Range("B1").Formula = "=COUNTIF(A$1:A$" & myLastRow & ",A1)"
Range("B1").AutoFill Destination:=Range("B1:B" & myLastRow)
Range("B1:B" & myLastRow).Select
' Turn formula into values
Columns("B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
' Delete dup rows
For i = myLastRow To 1 Step -1
If Cells(i, 2) > 1 Then Rows(i).EntireRow.Delete
Next i
' Delete temporary work column
Columns("B").Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub

 

[brettdj]

Thanks for the plug Zack

If you do use my addin:

select your column that you wish to delete on (either in one sheet or multiple sheets)

Run the addin

Application Scope - Range (should be your column)
Search Option - Row search (so that it works on the entire row)
Output Choices - Delete Duplicates

and then put a tick against the "completely remove row (not just contents)" box to cull the row completely

Cheers

Dave

 

[Zack Barresse]

Thanks for the plug Zack

What are you kidding Dave? That's one of the best tools ever developed for Excel. I have an obligation to plug it!

 

[mark.wagner]

I worked this one up some time back. not quite so invoved.

Public Sub Delete_duplicate_rows()
' This macro deletes rows with duplicates in the first column of a range selection.
' Or will delete all rows with duplicates in the same column as a single selected cell.
' Blanks are NOT duplicates.
' Mark Wagner, CPA 7/24/2005

Dim rowcounter As Long
Dim Cellvalue As Variant
Dim Rng As Range
Dim Target As String

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count = 1 Then

Columns(ActiveCell.Column).Select

End If

Target = InputBox("This Macro will delete duplicate rows in the column you have selected." & _
Chr(13) & Chr(10) & "Enter the number of duplicate rows you want to KEEP.", _
"Delete Duplicates Macro", 1)

If Target = "" Then

GoTo EndMacro

End If

Set Rng = Selection.Rows
' ActiveSheet.UsedRange.Rows

For rowcounter = Rng.Rows.Count To 1 Step -1
Cellvalue = Rng.Cells(rowcounter, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), Cellvalue) > Target Then
Rng.Rows(rowcounter).EntireRow.Delete

End If
Next rowcounter

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub