Need some help deleting duplicate values in Excel

[Shammikit]

Hello, I have an excel sheet with duplicate phone numbers in a specific column. I want to keep the first number and delete the rest of it's duplicates without deleting it's rows or shifting up any of the cells. Any guidance to create a formula or some other method if any would be very helpful. Thanks.

[TRS80]

https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d

[Shammikit]

1 minute ago, TRS80 said:

https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d

This deletes entire rows 

[TRS80]

2 minutes ago, Shammikit said:

This deletes entire rows 

Step 1 says to select the range of cells that has the dupes. 

[mariushm]

 

 

Sort your table by the phone column

Add a column to the right of your data

Put a formula     = IF (  phonecolumn[ROWNUMBER]  <> phonecolumn[ROWNUMBER-1] , 0 ,1 )

Double click cell corner so that the formula is copy pasted / propagated all the way to the last row

 

Now the cells will have 1 if the phone number is identical to previous row's phone number.

 

So now you can enable filter on the top row (go on row, press Ctrl+Shift + L  or use the menu option)  and you can select only the rows with 1  (as in rows that have same number as previous row)

Now you can select the phone numbers (drag mouse over the cells or Shift + down / up ) and hit DELETE to erase cells

 

Disable filter to see your whole table again.

 

 

[Shammikit]

7 hours ago, mariushm said:

 

 

Sort your table by the phone column

Add a column to the right of your data

Put a formula     = IF (  phonecolumn[ROWNUMBER]  <> phonecolumn[ROWNUMBER-1] , 0 ,1 )

Double click cell corner so that the formula is copy pasted / propagated all the way to the last row

 

Now the cells will have 1 if the phone number is identical to previous row's phone number.

 

So now you can enable filter on the top row (go on row, press Ctrl+Shift + L  or use the menu option)  and you can select only the rows with 1  (as in rows that have same number as previous row)

Now you can select the phone numbers (drag mouse over the cells or Shift + down / up ) and hit DELETE to erase cells

 

Disable filter to see your whole table again.

 

 

Thanks