Finding duplicates in specific cells across spreadsheets

[Bllets]

I'm working on an Excel project where I need to the cells to change fill colors if they have duplicate values. In a single spreadsheet it is easy with conditional formatting and I have done this, but I need it to check for duplicates in specific cells in the other two spreadsheets as well.

I've found several suggestions for rows and columns, but I have been unable to do so for specific cells, which is why I'm here.

First spreadsheet it is the following cells:

B16;F10;F5;F23;I15;M23;M13;M10;M6;P6;P9;P12;P15;P18

Second spreadsheet it is the following cells:

B7;E7:F7;D10:F10;D14:F14;D18:F18;D22:F22;B26;B31;E31:F31;I31;L31:M31;I26;K22:M22;K18:M18;K14:M14;K10:M10;I7;L7:M7;N4:O4;S4:T4;P7;S7:T7;R10:T10;R14:T14;R18:T18;R22:T22;P31;S31:T31

Third spreadsheet it is the following cells:

B7;D16;B20;F20;E7;I7;H20;J17;L20;L7;N13;N10

There is no specific rows or columns being used, and further more there are several cells in the spreadsheet that does not need to be check for duplicates, since they need to be duplicates.

I'm pretty sure this is possible, what I fear is that it is in macro territory and I have no clue about that what-so-ever.

 

[Luke M]

I would define each of the ranges/groups that you stated above with a named range, e.g. Rng1, Rng2, Rng3. Then, to check for duplicates, your CF formula is:
=COUNTIF(Rng1,CellReference)+COUNTIF(Rng2,CellReference)+COUNTIF(Rng3,CellReference)>1

where CellReference is the address of the cell getting the CF. Assuming the cell is within one of the 3 ranges, this should work.

 

[lohithsriram]

Hi Luke,

Thanks for solving this. I was cracking my head from more than an hour about combining the named ranges from different sheets. Just simple and elegant answer. Thanks.

 

[Bllets]

Tried this with CF and had no success. I then tried the formula in an ordinary cell and got #VALUE, so it doesn't recognize the named range in the function. It does however show that the range has marked all the desired cells, so the range is working, it just does not work in the function.

 

[SirJB7]

Hi, Bllets!
I assume you have replaced Rng1, Rng2 and Rng3 by the actual ranges (e.g., A2:A10, B2:B10, C2:C10 or whatsoever they're in your particular case) and CellReference by the cell with the test condition (e.g., D2 or your's one), haven't you?
If not, please proceed as described; if yet, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

 

[Bllets]

I'm replacing Rng1, Rng2 and Rng3 with the names of my named ranges.

However they don't work with COUNTIF since the values of the named ranges are:

='Group1'!$F$5;'Group1'!$B$16;'Group1'!$F$10;'Group1'!$F$23;'Group1'!$I$15;'Group1'!$M$23;'Group1'!$M$13;'Group1'!$M$10;'Group1'!$M$6;'Group1'!$P$6;'Group1'!$P$9;'Group1'!$P$12;'Group1'!$P$15;'Group1'!$P$18

If i simply change it to something like:

='Group1'!$F$5:$P$18

It works, but does not fix my current situation.

If not, please proceed as described; if yet, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

The current excel documents contain company information I cannot release, so I'm creating something similar, but it will take some time. I'm not an excel guy by default, but I'll throw one up as soon I get around to it.

 

[SirJB7]

Hi, Bllets!

Defining a named range with all the cells you posted won't do the job since COUNTIF doesn't work as COUNTA does, for example. So if you could spend a helper range, e.g. Z1:Z10, then placing the ='Group1'!$F$5 formula in Z1 and so, you'd be able to use the COUNTIF function on the contiguous range.

And about privacy concerns or corporate policies, maybe you've missed this:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."
which is here:
http://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705
within here:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regards!

 

[Luke M]

Sorry Bllets, I missed the fact that your cells were discontinuous within each worksheet.
Might I suggest a re-arrangement of the data on your worksheets? If you need to be checking a block of cells for duplicates, I would think it would be easier if they were grouped together to at least compare visually.

If that's not a viable option, SirJB7's suggestion of using a helper column is probably your best bet.