+ Reply to Thread
Results 1 to 4 of 4

Compare, Find and Highlight Duplicates in multiple sheets

  1. #1
    Registered User
    Join Date
    10-19-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,977

    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. #3
    Registered User
    Join Date
    10-19-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Compare, Find and Highlight Duplicates in multiple sheets

    Quote Originally Posted by Pete_UK View Post
    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. #4
    Registered User
    Join Date
    10-19-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Compare, Find and Highlight Duplicates in multiple sheets

    Quote Originally Posted by Pete_UK View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight duplicates across multiple sheets
    By Monkihunta in forum Excel General
    Replies: 0
    Last Post: 09-09-2013, 10:53 AM
  2. Replies: 8
    Last Post: 08-23-2013, 03:57 PM
  3. How to find duplicates across multiple sheets?
    By david08 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-16-2013, 06:04 AM
  4. Replies: 1
    Last Post: 06-13-2012, 06:36 AM
  5. Replies: 5
    Last Post: 07-05-2011, 06:25 PM

Bookmarks

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