39. Excel Tips - Remove Duplicates from a sorted list

Nick's picture


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:
Remove Duplicates Sorted List
and you want to end up with the duplicates removed:
Remove Duplicates Sorted List

Step1:
Enter the following formula in the cell adjacent to the 1st data cell:
Remove Duplicates Sorted List
This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.

Step2:
Copy the formula down
Remove Duplicates Sorted List

Step3:
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.
Remove Duplicates Sorted List

Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
Remove Duplicates Sorted List

Step5:
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:
Remove Duplicates Sorted List

Video Training on how to Remove Duplicates:

Removing duplicates

Hi! nice tip, I use it too, but for people with Excel 2007 and 2010 you have another alternative - click the Remove Duplicates button

It can be done easier

Always you can try to do it using "RemoveDuplicates" argument instead of building an formula (that won`t work if data are not sorted).
Best Regards,
Gregory