Conditional Formatting Duplicate Values (Upper & Lower Case)

**Orangeworker** · 03-04-2010, 09:09 PM

Hi,

Can someone help me out with a conditional formatting formula for duplicate values? I have two columns of data, one column is lower case, the other column is upper case. I've tried to use the built in formula, but it's not working -- I'm guessing it's due to upper/lower case.

Thanks.

**mdbct** · 03-04-2010, 10:29 PM

You can use the COUNTIF function:
if you want to check to see if a value in the A column matches a value in the B column use (with A1 selected). Use =Countif(B:B,A1)>0 as your formula. I've attached an example.

**Orangeworker** · 03-05-2010, 12:28 AM

MDBCT,

I can't seem to get the formula to work with my data. Could you look at my Excel file?

Thanks for your response.

**mdbct** · 03-05-2010, 12:41 AM

It is not working correctly because there is a space at the end of the values in the B column that is not present in the A column.

**Orangeworker** · 03-05-2010, 01:01 AM

I'm not sure what you mean, could you highlight what you're talking about?

Thanks.

**DonkeyOte** · 03-05-2010, 04:01 AM

mdbct is making the point that if you compare the length of the cells in Col A to their equivalent in Col B you will find Col B is longer - it has a trailing space.

Native Excel for the most part is not case sensitive (apple = Apple) - there are a few functions where this does not hold true but neither Match nor Countif are case sensitive.

It follows then that the failed duplicate test is down not to case sensitivity but to the fact that "apple" <> "Apple "

If you can't alter the original values (advised) you need to account for the trailing space either by

a) using Wildcard in Countif - open to false positives

b) using Trim in the Match
(I would suggest you use a Trim based approach if the originals are to be left as they are).

Regards your file... it's not really clear which cells you want to Conditionally Format.

If we assume for sake of demo you wish to highlight all instances of a match (ie A & B values simultaneously) then with A6:B11 highlighted (having selected A6 first) apply a Conditional Formula rule of:

Please Login or Register  to view this content.

using your sample the above would format B6 and A10...

The above takes advantage of the fact that with Conditional Formatting

a) formulae are processed as Arrays by default

b) errors evaluate as False

c) conditional formatting is applied where result is True (and in Excel any number other than 0 is True)