39. Excel Tips - Remove Duplicates from a sorted list
For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.
With a bit of practice you can become very quick at it.
Suppose you have the following data:
and you want to end up with the duplicates removed:
Enter the following formula in the cell adjacent to the 1st data cell:
This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.
Copy the formula down
Select the 1st heading (B2), and press in sequence:
ALT then d then f then f.. this turns on the data filter.
Now filter for TRUE (click on the arrow at cell C2, and select TRUE)- this selects only the duplicates.
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
Turn off the data filter by using the shortcut: ALT then d then f then f, and clear the formulae that you added... There you have it:
Video Training on how to Remove Duplicates: