How to remove entire rows with one specific matching cell.

[veer-india]

i AM A high school student.
In this excel document there is list of 100000 doctors. With their unique registeration number in a cell. Like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]Doc1[/TD]
[TD]1234[/TD]
[TD]adhh[/TD]
[TD]rnier[/TD]
[/TR]
[TR]
[TD]Doc2[/TD]
[TD]5678[/TD]
[TD]dscu[/TD]
[TD]dcwofn[/TD]
[/TR]
[TR]
[TD]Doc3[/TD]
[TD]9012[/TD]
[TD]ijwo[/TD]
[TD]dnw[/TD]
[/TR]
[TR]
[TD]Doc1[/TD]
[TD]1234[/TD]
[TD]fne[/TD]
[TD]oeirfj[/TD]
[/TR]
</tbody>[/TABLE]

Here entire row is not duplicate But the doctor appears in document 2-3 times. Having the same unique registration number. How i can remove the rows on the basis of one duplicate registeration number. ?
Thanks.

 

[Deman]

Should work for you

Private Sub RemoveDup()
   '
   ' NOTE: You must select the first cell in the column and
   ' make sure that the column is sorted before running this macro
   '
   ScreenUpdating = False
   FirstItem = ActiveCell.Value
   SecondItem = ActiveCell.Offset(1, 0).Value
   Do While ActiveCell <> ""
      If FirstItem = SecondItem Then
             
        ActiveCell.Offset(1, 0).Select
         Selection.EntireRow.Delete
         ActiveCell.Offset(-1, 0).Select
         SecondItem = ActiveCell.Offset(1, 0).Value
      Else
        ActiveCell.Offset(1, 0).Select
        FirstItem = ActiveCell.Value
        SecondItem = ActiveCell.Offset(1, 0).Value
      End If
   Loop
   ScreenUpdating = True
End Sub

 

[Special-K99]

Remove all rows or just the duplicates leaving one row? You want to leave the first row?

 

[AlanY]

first of all, sort them with their registration (Col B) or names (Col A).
add a helper column outside the dataset, Col G, say.

let say you sorted Col B,
put a simple formula if(B1=B2,"Y","N") and copy down, put a filter on Row 1, from the dropdown menu select "Y" and delete them all.

 

[veer-india]

Want to keep 1st row. All other removed.