# Compare, Find and Highlight Duplicates in multiple sheets

1. ## Compare, Find and Highlight Duplicates in multiple sheets

I have one base document I have to compare to 15 sheets. I am looking for like pieces of equipment in one main sheet versus the other 15. Specifically I am comparing 1 Column (A) vs another Column (D) in another sheet. I would like the 15 sheets to be highlighted when the duplicate is found. I have tried both formulas and conditional formatting. I am using Windows 7 and Office 2010. Thank you

2. ## Re: Compare, Find and Highlight Duplicates in multiple sheets

In your subsidiary sheets you could have a formula like this in a helper column:

=IFERROR(MATCH(D2,Master!A:A,0),"")

assuming the data starts on row 2. A number appearing in a cell in that column will indicate a duplicate, but it you want to highlight the cell then you could use conditional formatting looking at that column, with a CF formula like this:

=\$H2<>""

assuming the helper column is H.

Hope this helps.

Pete

3. ## Re: Compare, Find and Highlight Duplicates in multiple sheets

Originally Posted by Pete_UK
In your subsidiary sheets you could have a formula like this in a helper column:

=IFERROR(MATCH(D2,Master!A:A,0),"")

assuming the data starts on row 2. A number appearing in a cell in that column will indicate a duplicate, but it you want to highlight the cell then you could use conditional formatting looking at that column, with a CF formula like this:

=\$H2<>""

assuming the helper column is H.

Hope this helps.

Pete
I forgot to tell you this is my first time trying this so if you could be a little more descriptive on the CF I would appreciate it. Thank You

4. ## Re: Compare, Find and Highlight Duplicates in multiple sheets

Originally Posted by Pete_UK
In your subsidiary sheets you could have a formula like this in a helper column:

=IFERROR(MATCH(D2,Master!A:A,0),"")

assuming the data starts on row 2. A number appearing in a cell in that column will indicate a duplicate, but it you want to highlight the cell then you could use conditional formatting looking at that column, with a CF formula like this:

=\$H2<>""

assuming the helper column is H.

Hope this helps.

Pete
Pehaps this link is a good starting point?http://stackoverflow.com/questions/1...cate-any-dupli

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1