Hello,
What is the simplest/quickest way to identify duplicate rows in an Excel spreadsheet, but be able to distinguish between the first occurrence of the duplication and all subsequent occurrences?
I am aware of two functions in Excel which act on duplicate entries.
One function is:
Data > Remove Duplicates
which, depending on the options chosen, completely deletes duplicate rows. However, the first or initial occurrence of the row is never deleted, only subsequent occurrences.
The other function is:
Conditional Formatting > Highlight Cell Rules > Duplicate Values
which enables you to quickly identify duplicates (both first and subsequent) in a selected range by giving them a pink background.
I use both of these functions frequently but suppose you want sort of a hybrid of the two functions in which only the rows which would be removed if the first function were applied are given a colored background.
For example, suppose you want to selectively remove only some duplicated rows and you never want to remove the first occurrence?
To do this, I sometimes apply the Duplicate Values function and then filter one of the columns by the background color (pink) to show only those rows which are duplicates. However, when I then look at a given row, I can't tell if it's duplicate is above or below in the spreadsheet. In that case, the only way to be certain you are not deleting the first occurrence of a duplicate row is to begin from the bottom and work up.
Thanks
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.