How do I prioritize preferred Duplicate rows to keep, while using the Remove Duplicates function?

Copper Contributor



I have a sheet of 3000 rows; ~300 of them are duplicate entries to be removed.


Most of my rows have a State abbreviation assigned to them, but a few hundred have a placeholder abbreviation where the State is unknown.


I'm removing duplicate rows/entries by parsing for identical Phone numbers. Because they were all pulled from various spreadsheets, only the Phone numbers reliably come up and flag an entry as identical, with the remaining data in the cells left and right of it having varying degrees of completion.


I'd like to prioritize keeping the rows that have more information, i.e., a known and assigned State. I don't want to keep the duplicate entries with the Placeholder abbreviations, having them staying to overwrite their more complete counterparts.


How can I accomplish this?


Thank you.

2 Replies


Data > Remove Duplicates always keeps the first (top) of a set of duplicate entries, and deletes the ones below.

So if you can sort your data so that the placeholder state abbreviations are at the bottom, you'll get the result you want. You might - for example - use ZZ as placeholder.

@Hans Vogelaar 


Beautiful, simple solution with good information to know going forward. Thanks.