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.