Deleting Duplicate Enteries
Posted by Tim Watson on August 06, 2001 2:29 PM
I often work with lists that cover a time period (ie, All deliveries accomplished by a delivery company between 1/1/01 & 7/30/01). These deliveries are being made to roughly the same group of people... (Some people in the group ((known as 'consigness'))receive monthly deliveries, some only 1 or 2 occasions over the time frame.) The list may show the name 'Bill Jone' in 'Alameda, CA' five times. I need to be able to take a given list and remove any duplicate consignees to eventually end up with a complete list of the total number of consigness being delivered by a given delivery company. Is there a way to remove duplicate enteries in Excel 2000? Help! this has been driving me nuts!
Posted by Joe Barocio on August 06, 2001 5:02 PM
You can retrieve all the unique records in a list with |Data| Filter|Advanced Filter|. Once there, click "Copy to another location", highlight the location for the retrieved records, highlight the data you want to filter, click "Unique records only", and click OK. Go to the location for the retrieved records to find a list of unique names.
Posted by Damon Ostrander on August 06, 2001 10:28 PM
Hi Tim,
Here is a macro that makes this easy. Just select the range that contains the columns that have the duplicate data (in your example, the columns containing the name and the city,state. This range should also contain all the rows of interest. Then just run the macro, and all the rows with that range that have duplicate values for both name and city,state will be deleted.
Happy computing.
Damon
Sub DelDups()
' Deletes duplicate rows in the selected range.
' All columns in the selected range must be identical for
' a row to be deleted. The entire row, not just the selected
' cells in the row, will be deleted if a duplicate is found.
' The first instance of the duplicate row is the copy that
' will be retained.
Dim iRow As Long
Dim jRow As Long
Dim iCol As Integer
Dim LastRow As Long 'The last row in the selected range
Dim FirstRow As Long 'The first row in the selected range
Dim FirstCol As Integer
Dim LastCol As Integer
Dim DelCount As Long 'The count of duplicate rows removed
Dim DupFound As Boolean 'True if duplicate row found
DelCount = 0
FirstRow = Selection.Row
LastRow = FirstRow + Selection.Rows.Count - 1
FirstCol = Selection.Column
LastCol = FirstCol + Selection.Columns.Count - 1
For iRow = FirstRow To LastRow - 1
For jRow = iRow + 1 To LastRow
DupFound = True
For iCol = FirstCol To LastCol
DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol))
If Not DupFound Then Exit For
Next iCol
If DupFound Then
' Duplicate row found--delete it
Rows(jRow).Delete
LastRow = LastRow - 1
DelCount = DelCount + 1
End If
Next jRow
Next iRow
Beep
MsgBox DelCount & " duplicate rows deleted.", _
vbInformation, "Duplicate Removal Results"
End Sub
