Results 1 to 8 of 8

Highlighting all duplicate values across multiple worksheets

This is a discussion on Highlighting all duplicate values across multiple worksheets within the Excel Questions forums, part of the Question Forums category; Hi, first off I must apologize for being new to this. I'm having an issue, similar to some I've seen ...

  1. #1
    New Member
    Join Date
    Feb 2009
    Location
    Kansas City, MO
    Posts
    4

    Exclamation Highlighting all duplicate values across multiple worksheets

    Hi, first off I must apologize for being new to this. I'm having an issue, similar to some I've seen here. First problem: I'm trying to Highlight duplicated cells across multiple (6) worksheets. The data is always in column 4 and it has to be exactly the same for it to highlight. Second Problem: Is it possible to highlight with different colors cells that are duplicated on Worksheet 1 & 2 vs Worksheet 1 & 3. Basically if A and B match, the cells on both sheets would be red. If A and C matched, the cells on both sheets would be green and if the data matched on A, B, and C then the cells on all 3 sheets would be green. I'm not sure if it's even possible to write a VB script to accomplish that. Any help would be greatly appreciated, I'm losing my mind using the "find" command cell by cell and worksheet by worksheet. Thanks in advance!

  2. #2
    Board Regular
    Join Date
    May 2007
    Location
    Brisbane, Australia
    Posts
    1,035

    Default Re: Highlighting all duplicate values across multiple worksheets

    Use conditional formatting and a couple of "countif" formulas

    mind you - you can only have 3 "conditions" with this method.
    One of the reasons I love Excel so much is every time I think I have it figured out someone shows me a better/more efficient/ cleaner or faster method to do what I have been doing the long way for years....


    http://www.rentacoder.com/RentACoder...RL=AId_7206185

  3. #3
    New Member
    Join Date
    Feb 2009
    Location
    Kansas City, MO
    Posts
    4

    Default Re: Highlighting all duplicate values across multiple worksheets

    haven't used the "countif" funtion before, but I've looked up some examples.
    Let me try and simplify.. Lets say 2 worksheets and a cell in column 4 is the one that has to be a duplicate for it to highlight the cell on both sheets. I can build from there I think, I've pillaged various scripts thus far from this site and been able to analyze and build from them, but this problem eludes me.

  4. #4
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,773

    Default Re: Highlighting all duplicate values across multiple worksheets

    You can add some columns to tell you what sheets each value is also found in.

    If the values are in column D, then in E try this:

    =IF(ISNA(MATCH(D1,Sheet2!$D$1:$D$200,0)),"","Sheet2")
    ...and copy down. The words Sheet2 will appear next to the ones that match

    Do the same thing in the next column to check Sheet3. On Sheet2, create two columns to compare Sheet1 and Sheet3, and on Sheet3 it compares the first two sheets.

    You could even do it all in one cell, but that's a "taste" thing:

    =IF(ISNA(MATCH(D1,Sheet2!$D$1:$D$200,0)),"","Sheet2")&IF(ISNA(MATCH(D1,Sheet3!$D$1:$D$200,0)),"","Sheet3")

    If you're dedicated to the lighting up of cells, you could then do so by examining the helper columns and using conditional formatting to change the color of the original data cells.

    But I would use the Sheet2, Sheet3 flag(s) in the helper columns to suffice for IDing the matches and work from there. That's as clear as anything.

    Sheet1

    *DE
    1dogSheet3
    2catSheet2Sheet3
    3birdSheet2Sheet3

    Spreadsheet Formulas
    CellFormula
    E1=IF(ISNA(MATCH(D1,Sheet2!$D$1:$D$200,0)),"","Sheet2")&IF(ISNA(MATCH(D1,Sheet3!$D$1:$D$200,0)),"","Sheet3")
    E2=IF(ISNA(MATCH(D2,Sheet2!$D$1:$D$200,0)),"","Sheet2")&IF(ISNA(MATCH(D2,Sheet3!$D$1:$D$200,0)),"","Sheet3")
    E3=IF(ISNA(MATCH(D3,Sheet2!$D$1:$D$200,0)),"","Sheet2")&IF(ISNA(MATCH(D3,Sheet3!$D$1:$D$200,0)),"","Sheet3")


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by jbeaucaire; Feb 6th, 2009 at 04:42 AM.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  5. #5
    Board Regular
    Join Date
    May 2007
    Location
    Brisbane, Australia
    Posts
    1,035

    Default Re: Highlighting all duplicate values across multiple worksheets

    Say you have column 4 in sheet1

    and a value "123" in cell D3

    do you want it to highlight if there are ANY cells with 123 in Sheet2 Column 4?

    or only if Cell D3 in sheet2 contains 123


    - Either way is quite possible
    One of the reasons I love Excel so much is every time I think I have it figured out someone shows me a better/more efficient/ cleaner or faster method to do what I have been doing the long way for years....


    http://www.rentacoder.com/RentACoder...RL=AId_7206185

  6. #6
    New Member
    Join Date
    Feb 2009
    Location
    Kansas City, MO
    Posts
    4

    Exclamation Re: Highlighting all duplicate values across multiple worksheets

    Ugh! sorry but at 4Am I'm soaking this up like a steel sponge. I've been staring at this data too long. Ok, I was a bit off in my original post. Thankfully you guys are catching it though. It should have been Column E not "column 4" Basically you've got it right if for instance E4 has a value that matches any data in Column E on any of the worksheets I would like to highlight it. as said, it's 6 sheets of data, but I think I can work up from just 2 with an example. Thanks again guys, I really appreciate the help thus far.

  7. #7
    Board Regular
    Join Date
    May 2007
    Location
    Brisbane, Australia
    Posts
    1,035

    Default Re: Highlighting all duplicate values across multiple worksheets

    Select column E in sheet 2 - and name the range "insheettwo"

    then select column E in Sheet1 choose conditional formatting - then choose "formula is" and paste...

    =COUNTIF(insheettwo,E1)>0
    One of the reasons I love Excel so much is every time I think I have it figured out someone shows me a better/more efficient/ cleaner or faster method to do what I have been doing the long way for years....


    http://www.rentacoder.com/RentACoder...RL=AId_7206185

  8. #8
    New Member
    Join Date
    Feb 2009
    Location
    Kansas City, MO
    Posts
    4

    Talking Re: Highlighting all duplicate values across multiple worksheets

    I copied the Countif function and it worked perfectly. A bit of tweaking and I got the first two sets compared and the duplicates highlighted green!! Thank you guys so much for the quick replies! I had already decided to call in and beat my head against the desk until either the cells changed color or I started seeing them as different colors! But once again, that was what I needed to get started. I really miss my Excel 2003 (using 2007 now)

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com