Excel how to remove both items if a duplicate


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

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.

excel how to remove both items if a duplicate 22758 - Excel how to remove both items if a duplicate

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>)

=COUNTIF($A$2:$A$26,A2)

Where the entries are in column A down to row 26. A2 is the current cell.

excel how to remove both items if a duplicate 22759 - Excel how to remove both items if a duplicate

In an Excel table the formula can be easier to read:

=COUNTIF([Dwarves],[@Dwarves])

The list does not have to be in alphabetical order or have the duplicates in neighboring rows.

excel how to remove both items if a duplicate 22760 - Excel how to remove both items if a duplicate

A little surprisingly the CountIf test is NOT case-sensitive. All three ‘Gabby’ rows are counted together despite the capitalization.

excel how to remove both items if a duplicate 22767 - Excel how to remove both items if a duplicate

Now you can filter by the count column to show only unique (count = 1) values.

excel how to remove both items if a duplicate 22761 - Excel how to remove both items if a duplicate

Having a Count column can be really useful for other reasons. It helps detect unexpected duplications in what should be a unique list.

subs profile e1563205311409 - Excel how to remove both items if a duplicate
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address