troubleshooting Question

Identify duplicated rows in Excel which are not the first occurrence

Avatar of WeThotUWasAToad
WeThotUWasAToad asked on
Microsoft ExcelMicrosoft Office
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.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 12 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 12 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004