Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Excel has an in-built feature to remove duplicates from a list but what if you want to delete both items if they are duplicated or more than two?
Remove Duplicates is tucked away on the Data tab, Data Tools | Remove Duplicates. It will ensure that an Excel list/table has only unique values for the column selected.
That’s OK for most situations. In this case, it’ll remove the extra rows for Happy and Jumpy – leaving just one row for each.
Remove both duplicates
Sometimes you need to remove BOTH entries if there’s any duplication. For the above example that means removing both rows for Happy and Jumpy.
The solution is to count the number of entries using Countif()
=COUNTIF(<column range),<cell to test>)
Where the entries are in column A down to row 26. A2 is the current cell.
In an Excel table the formula can be easier to read:
The list does not have to be in alphabetical order or have the duplicates in neighboring rows.
A little surprisingly the CountIf test is NOT case-sensitive. All three ‘Gabby’ rows are counted together despite the capitalization.
Now you can filter by the count column to show only unique (count = 1) values.
Having a Count column can be really useful for other reasons. It helps detect unexpected duplications in what should be a unique list.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.