Highlighting all duplicate values across multiple worksheets

[McNerney00]

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!

 

[Makrini]

Use conditional formatting and a couple of "countif" formulas

mind you - you can only have 3 "conditions" with this method.

 

[McNerney00]

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.

 

[jbeaucaire]

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.

Excel Workbook
	D	E
1	dog	Sheet3
2	cat	Sheet2Sheet3
3	bird	Sheet2Sheet3
Sheet1

 

[Makrini]

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

 

[McNerney00]

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.

 

[Makrini]

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

 

[McNerney00]

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)