Finding Dups in one column then delete leaving one

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

I have a list of Vendor numbers in Column A, I need to find the duplicates
that are in column A then delete the duplicates but leave one. How and where
do I write the formula for this?
 
If you are using Excel 2007, select column A, then select Remove Duplicates
on the Data ribbon.

In Excel 2003 & earlier, use 2 helper (empty) columns on the sheet that has
the duplicates. Enter this formula in row 1 in one helper column:
=Row()
Enter this formula in row 1 in the other helper column:
=IF(COUNTIF($A$1:$A1,A1)>1,0,1)
Copy these two cells down through the last row of data (maybe the last
vendor number in column A). Press F9 to recalculate the spreadsheet. Now
select the two helper columns. Copy & paste special them in place as values.
Select all the data on the sheet and sort it by the second helper column (the
one that had the COUNTIF formula). Delete all the rows with a zero in that
column - these are the duplicates. Sort all the data again by the first
helper column; this puts the rows back in their original order minus the
deleted duplicates. Finally, delete the two helper columns.

Hope this helps,

Hutch
 
You can use code like the following:


Sub DeleteDupRows()
Dim StartRow As Long
Dim EndRow As Long
Dim RNdx As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
StartRow = 1
EndRow = Cells(StartRow, "A").End(xlDown).Row
For RNdx = EndRow To StartRow Step -1
If Application.CountIf(Range(Cells(1, "A"), _
Cells(RNdx, "A")), Cells(RNdx, "A").Value) > 1 Then
Rows(RNdx).Delete
End If
Next RNdx
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub





Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top