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!
Welcome to the forum.
It would help if you attached a sample Excel workbook, so we can see how your data is laid out. You can do this by following the guidelines in the second yellow banner at the top of the screen.
Hope this helps.
Pete
Hi there,
I can't upload my excel doc for privacy reasons but I have a long list of URLs in Column A and Column B. I just want to know which values are duplicates in both columns but for exact matches only.
Thanks
Welcome to the forum.
You can prepare a small sample file.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Please find attached a sample worksheet.
When I highlight Column A & B -> Select Conditional Formatting -> Highlight Cell Rules -> Duplicate Values, notice how many matches are found because the case is insensitive.
I only want these words to be highlighted in red because they are exact matches:
say
assertive
potential
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. I shall do it for you THIS time: https://www.mrexcel.com/board/thread...tches.1260414/)
Use this CF formula
Formula:
Please Login or Register to view this content.
adjust the last row from 45 to your anticipated maximum last row.
CF rule for A1:
=AND(A1<>"",SUM(--EXACT(A1,$B$1:$B$1000))>0)
Applies to: =$A$1:$A$1000
CF rule for B1:
=AND(B1<>"",SUM(--EXACT(B1,$A$1:$A$1000))>0)
Applies to: =$B$1:$B$1000
Thanks all. Big thanks to 6StringJazzer as he posted the solution first. Thanks also AliGW.
My rule and Ali's rules are a little different, and I am not sure which one you need.
My rule looks at a cell in either column, and colors it if there is a duplicate in either column A or column B.
Ali's rules looks at a cell in column and colors it if there is a duplicate in column B. It looks at a cell in column B and colors it if there is a duplicate in column A.
Your description could be interpreted either way and it's not clear from context which one you need.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks