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:
Step1:
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.
Step2:
Copy the formula down
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.
Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
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:
Video Training on how to Remove Duplicates:
- Nick's blog
- Login or register to post comments
- 13025 reads
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