troubleshooting Question

Identify duplicated rows in Excel which are not the first occurrence

Avatar of WeThotUWasAToad
WeThotUWasAToad asked on
Microsoft OfficeMicrosoft Excel
12 Comments1 Solution31 ViewsLast Modified:

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.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros