Deleting repeated rows

  • Thread starter Thread starter anjanesh
  • Start date Start date
A

anjanesh

I got an excel file which has around 50k rows but should have around 25k
rows - some have got duplicated somehow - the first column that I have
is the ID which is unique - is there any way to delete rows having
repeated IDs ?

Thanks
 
Hi,

You could put a button in the sheet and use this code:

Private Sub CommandButton1_Click()
Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x),
Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub


Regards,
Bondi
 
This sounds like a one-time effort. Try this.
Select cell A1 and Insert... Columns
Now your unique IDs are in column B.
Type any character in cell A1 (will explain later)
Now select column A (the whole column should be highlighted)
Now Edit... Go To... Special... Blanks Then hit OK
The blank cells will be selected and the active one should be B2.
Type the formula =MATCH(B2,B$1:B1,0) and, while holding <ctrl>, hit
enter.
Again, Edit... Go To... Special... Formulas
Below Formulas, you have four option buttons. De-select Errors.
(Actually, the only one you need selected is Numbers) Then hit OK.
Edit... Delete... Entire Row then OK.
Last of all, you can delete column A.

What you are doing is trying to match each ID with those above it. If
it has no match, you get an error (#N/A) which means that, so far, it's
unique. Those that yield a number are conversely not unique and subject
to deletion. The special go to selected only the non-error which were
the numbers which you then deleted.
One other point. The original go to looking for blanks only selects
blanks down to the last row ever used on your worksheet.
 
Select cell A1 and Insert... Columns
Now your unique IDs are in column B.
Type any character in cell A1 (will explain later)
Now select column A (the whole column should be highlighted)
Now Edit... Go To... Special... Blanks Then hit OK
The blank cells will be selected and the active one should be B2.
Type the formula =MATCH(B2,B$1:B1,0) and, while holding <ctrl>, hit
enter.
Click to expand...

I tried that and Excel comes back with a message that the formula
contains an error.


Lars
Stockholm
 
Back
Top