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

Doc1	1234	adhh	rnier
Doc2	5678	dscu	dcwofn
Doc3	9012	ijwo	dnw
Doc1	1234	fne	oeirfj

<tbody>
</tbody>

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.