+ Reply to Thread
Results 1 to 6 of 6

Highlight duplicate values on multiple worksheets in the same workbook

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Question Highlight duplicate values on multiple worksheets in the same workbook

    Hello,
    I have a workbook that has five worksheets listing warehouse inventory items. One worksheet for 2013, 2012, 2011, 2010 and 2009. In column b of each worksheet is a column B with a heading of "Item Number". There are hundreds of item numbers on each worksheet.

    I would like to be able to find and highlight item numbers that appear in multiple years. Preferably color coded showing appears in five, four and three years. If that's too difficult than just items that appear in all five years. I tried conditional formatting, but have been unsuccessful.

    Lastly, is there a way to list any duplicate item numbers on a new worksheet.

    Thanks so much for our help!
    Eric

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Highlight duplicate values on multiple worksheets in the same workbook

    1) Create NAMED ranges of each of the column B on each sheet.

    2013B
    2012B
    2011B
    2010B
    2009B

    2) now apply conditional formatting to highlight the cells based on how many times the value in the current cell is MATCHED to each of those ranges.

    =SUM(COUNTIF(2013B, B2), COUNTIF(2012B, B2), COUNTIF(2011B, B2), COUNTIF(2010B, B2), COUNTIF(2009B, B2))>=3

    3) Apply that same CF to the column B values on each sheet, they will color accordingly.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-18-2013
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: Highlight duplicate values on multiple worksheets in the same workbook

    Thanks so much for the response. I named the ranges _2013inventory, _2012inventory, etc. I got an error message when using 2013B, 2012B, etc.
    I can change the formula to reflect the range names, but I'm uncertain as to where that formula goes. I selected the range of item numbers on the 2013 tab then selected conditional formatting on the home tab. Then I selected "Highlight Cell Rules" on the drop down. There are multiple choices that come up in another drop down like "greater than" & "less than". What would I select and where would the formula go? I am not sure how to apply the CF with the formula string you gave me.

    Sorry if I'm missing something obvious. I am using Excel 2011 for Mac.

    Eric

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Highlight duplicate values on multiple worksheets in the same workbook

    If you highlighted B2:B100, then the you would use the option "Use a formula...." and use the formula demonstrated as it already refers to B2.

  5. #5
    Registered User
    Join Date
    08-18-2013
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: Highlight duplicate values on multiple worksheets in the same workbook

    Thank you very much for your help! All set!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Highlight duplicate values on multiple worksheets in the same workbook

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

+ 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 duplicate values
    By abibayley in forum Excel General
    Replies: 3
    Last Post: 04-23-2012, 12:03 PM
  2. Need help on how to highlight duplicate values
    By avadakedava in forum Excel General
    Replies: 3
    Last Post: 04-10-2012, 11:27 AM
  3. Replies: 0
    Last Post: 06-29-2011, 02:36 AM
  4. Highlight duplicate row values
    By Brian Tam in forum Excel General
    Replies: 5
    Last Post: 08-27-2010, 04:54 PM
  5. Replies: 3
    Last Post: 12-04-2005, 04:10 PM

Tags for this Thread

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