Forum Discussion
Conditional Formatting Partial
Good morning,
In need of a little bit of help. My current situation is I can't figure out a formula to highlight partial matches. In my current excel I gather information from a scan then I filter it out to give me the text I want. Now from there I want that filtered info to match another sheet that same text followed with text after. Due to privacy reason I cant share my excel sheet but will provided a similar example.
So lets say column D is the filter info from my scan and my list is from column A (in real word list and filtered info are in 2 separate sheets but same book). on the top you can see a formula I have been trying but doesn't always work. I want both sides (or sheets to highlight when it happens).
Using your example:
Select A2:A29 (or however far down you want).
A2 should be the active cell in the selection.
Create a conditional formatting rule with formula=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$D$1000))
Select D2:D29 (or however far down you want).
D2 should be the active cell in the selection.
Create a conditional formatting rule with formula=AND($D2<>"", COUNTIF($A$2:$A$1000, $D2&"*"))
9 Replies
Feels like thats the exact formula I was trying but wouldn’t work. Only difference I see is that I have multiple columns and rows to search from so not sure if that makes the formula change. Didn’t have a chance to try it as I had to leave work but will give it another try tomorrow.
for example, instead of
=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$D$1000), ISNUMBER(SEARCH("no", A2)))
I’m using
=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$G$1000), ISNUMBER(SEARCH("no", A2)))
so instead of just looking a column it’s an array and I think that’s what throws off the formula
Might just have to do a formula for each column
When I try that, it works correctly - at least, it appears that way to me:
What am I missing?
Was super tired and was putting in wrong info got it to work 😂 Your formula was correct and works.
What would be the formula if I wanted the stuff on the right to highlight with the same colors? (Whatever is in column D:G on your example)
Using your example:
Select A2:A29 (or however far down you want).
A2 should be the active cell in the selection.
Create a conditional formatting rule with formula=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$D$1000))
Select D2:D29 (or however far down you want).
D2 should be the active cell in the selection.
Create a conditional formatting rule with formula=AND($D2<>"", COUNTIF($A$2:$A$1000, $D2&"*"))
HansVogelaarthank you for the very quick response that was worked like a charm! I will mark it as a solution. Once I applied it to my excel sheet I noticed another twist I would like to add I hope you can give me a hand with that. I was to be able to be a certain color if the partial match also contains a yes or no. I will screenshot again what I mean,
In this situation if it finds a partial I would also like for it to look for a "yes" or a "no". The "yes or no" is not always in the place so I was thinking maybe a ISNUMBER(SEARCH(????) not sure, but if "yes" pink if "no" then blue
For A2:A29 (or further down), use the formulas
=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$D$1000), ISNUMBER(SEARCH("yes", A2)))
and
=AND(A2<>"", OR(LEFT(A2, 3)=$D$2:$D$1000), ISNUMBER(SEARCH("no", A2)))
