I have a list of partial text of names of clients, for example.
John Smith Peters
I want to conditional format another column if any of these partial matches are found.
John David Lesley Craig Brian Smith Arnold Palmer Linda Paul Shelly Johns Thomas Peters Brenda Young
Any help with this would be greatly appreciated!
Are you wanting this to be dynamic? In other words, do you want to be able to change the names you are looking for?
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes, AliGW, the list can expand too, it might be one partial name, it might be 50 or more.
If you don't need it to be dynamic, then create three new rules using the Format cells that contain ... option in the CF dialog, and choose Specific text from the drop-down list at the bottom.
Then I am afraid that you are going to need to, er, 'expand' on your requirement. Can you explain in more detail?Originally Posted by SirBrock
I have a column where I want to be able to enter partial names of clients. This column can be any size, but won't likely be more than 100.
I have another column of clients, which is also dynamically generated.
I want to use the list of partial names to apply conditional formatting to each client.
If the partial list is John, Clare, Sofia, Dominic, and the client's full name is John Smith, it will highlight that cell in red. If the name was Craig Anderson, then it would have no formatting.
I hope this helps.
Eek!
This is going to require quite a complicated CF formula, methinks. I shall have a ponder! Maybe one of the gurus here will be able to solve it immediately. Thanks for the clarification!
With the list of partial names in M1:M3, and main list in column A, starting in A2, try this formula for the cf rule.
=OR(ISNUMBER(SEARCH(M$1:M$3,A2)))
=OR(ISNUMBER(SEARCH(E$2:E$1000,G2)))
How can I tell it to ignore an empty cell in the E$2:E$1000 data? It is causing it to be constantly true.
As long as the empty cells are at the end of the list,
=OR(ISNUMBER(SEARCH(E$2:INDEX(E:E,MATCH("ZZZ",E:E)),G2)))
Should work.
Try this...
Data Range
Select the entire range A1:A10.
Conditional formatting...
Formula:
=SUM(COUNTIF(A1,"*"&C$1:C$3&"*"))
If you test that formula in worksheet cells it must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Last edited by Tony Valko; 12-12-2015 at 09:52 AM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks