Highlight duplicates for case-sensitive matches

[excelguy2024]

Hello,

Is there a way to make conditional formatting case sensitive?

I want to highlight the duplicate values in Column A and Column B. When I currently do this, all duplicates are highlighted regardless of their case. I want the exact matches only to be highlighted.

Thanks!

 

[dreid1011]

If you are using SEARCH(), you can replace it with FIND() as FIND() is case sensitive and SEARCH() is not. If you are not using these, could you show what CF formula you are using?

 

[Joe4]

Welcome to the Board!

The EXACT function works great if comparing one value to another, but this can be difficult if are comparing one value against a whole bunch of others cells.
Here is a link here that shows how it can be done: https://support.google.com/docs/thr...-formatting-that-is-also-case-sensitive?hl=en

 

[excelguy2024]

Hi there,

I'm not looking to replace the values, only highlight the duplicates for exact matches only (case sensitive).

I am currently selecting all data in Column A & Column B and then selecting Home -> Conditional Formatting -> Highlight Cell Rules -> Duplicate Values. The issue is that it highlights the duplicates regardless of their case (case insensitive).

Thanks

 

[dreid1011]

Try this for your CF rule, choose New Rule and Use a formula to determine which cells to format:

=SUM(--ISNUMBER(FIND(A1,$A$1:$A1)))>1

Of course, change the ranges to suit your data.

Book1
	A
1	Red
2	Orange
3	Yellow
4	green
5	green
6	Purple
7	Red
8	Orange
9	Yellow
10	green
11	green
12	Purple
13	Red
14	Orange
15	Yellow
16	green
17	Blue
18	Purple
19	Red
20	Orange
21	Yellow
22	green
23	green
24	Purple
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A24	Expression	=SUM(--ISNUMBER(FIND(A1,$A$1:$A1)))>1	text	NO

 

[excelguy2024]

Thanks for the response but it doesn't seem to working here. I've attached an example to clarify my request.

When I highlight Column A & B (below) in Excel -> Select Conditional Formatting -> Highlight Cell Rules -> Duplicate Values, many matches are found because the case is insensitive.

I only want the words in bold to be in red because they are exact matches:

Column A:

site
affair
hell
abandon
feign
say
make
assertive
revolutionary
potential
overlook

Column B:

dare
plan
finish
improve
society
Venus
reverse
answer
established
X-ray
mutter
instinct
page
mole
primary
ask
option
soldier
ghostwriter
declaration
SITE
AFFAIR
HELL
ABANDON
FEIGN
SAY
MAKE
ASSERTIVE
REVOLUTIONARY
POTENTIAL
OVERLOOK
sItE
AfFaIr
hElL
AbAnDoN
FeIgN
SaY
MaKe
aSsErTiVe
rEvOlUtIoNaRy
pOtEnTiAl
oVeRlOoK
say
assertive
potential

 

[Joe4]

You are NOT going to be able to do this with the built-in Conditional Formatting options available to you like that. As you can see, they are case-insensitive.
You are going to have to use the "Use a formula to determine which cells to format" option and write a formula, like shown in the link.

 

[excelguy2024]

You are NOT going to be able to do this with the built-in Conditional Formatting options available to you like that. As you can see, they are case-insensitive.
You are going to have to use the "Use a formula to determine which cells to format" option and write a formula, like shown in the link.

Agreed and this is the part I'm struggling with.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Highlight duplicates for case-sensitive matches
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Joe4]

Yeah, it does not appear to be an easy task. It would be easier if you were just comparing a list to a single value. But comparing two lists makes it a bit more tricky.
I do not use REGEX much myself, so I don't really know how to incorporate the formula from that link into your situation.
I found this other post, and the formula from "The NOmadic" seemed promising too, but I could not get it to work either:

https://www.reddit.com/r/excel/comments/jxyzpj/conditional_formatting_formula_help_needed_case

If it were me, I might try to use VBA instead and loop through all the values and check each one.