Conditional Formatting For A List Of Partial Matches In Excel!

[@tammytoo3124]

VERY well done! You somehow manage to train in a very informative yet still concise manner. Most either skip many important things in order to be concise, or go on and on in order to not miss anything. You need to bottle that skill and sell it. Take my money!

[@davidoquinn4568]

THANK YOU SO MUCH for this video. I’ve had this exact issue for a while and now it works! You have a new subscriber!

[@wayneedmondson1065]

Hi Jon. Great video and tips. Just for fun, here is another way to make the Begins With match: =SUM(--(LEFT(A3,5)=$F$3:$F$5)). Also, this one will make the match if the rule string is anywhere in the target string (case sensitive): =SUM(--ISNUMBER(FIND($F$3:$F$5,A3))). Fun to play with different ways to get the results. Thanks for sharing :)) Thumbs up!!

[@donniechen8718]

These tutorial videos are great! Appreciate your generosity and time for sharing!

[@justinoneill2837]

SUPER HELPFUL! Thankyou! The next step in this for me is... "How to highlight the rows cell ONLY if it has a value (not empty)"

[@shoaibrehman9988]

I would say good revision for me with full of explanation. Thanks

[@Excelambda]

Great Video!!! Also this can be consider if we are looking for patterns anywhere on the cells, not only at the beginning. =OR(ISNUMBER(SEARCH(b_w,$A3))) - we create a table D2·:D6 and define a name selecting the colum of the table as b_w

[@tomwelt1825]

Great topic and explanation, Jon. Super explanation for expanding the criteria list. How about an explanation for applying the conditional formatting when the database is expanded beyond those through row 20? Should be easy!

[@growth2509]

This is good use of conditional formatting

[@gregorytaylor2749]

Great tutorial! Thanks Jon!!👍

[@ExcelCampus]

Thanks Gregory! 🙌

[@iankr]

Thanks! Great stuff. I like to name ranges where it helps, so I would name that "Begins with" range used in the COUNTIF formula and use its name there, rather than the address. Makes it easier to audit.

[@ExcelCampus]

Great suggestion Ian! We've had a few people mention the same and will update the blog post with a section on this technique. We'll give you a mention there as well. Thanks! 🙂

[@iankr]

@@ExcelCampus Many thanks, Jon.

[@darrylmorgan]

Cool Tutorial With Really Great Tips...Thank You Jon :)

[@ExcelCampus]

Thanks Darryl! 🙏

[@Supermama88w]

Thank you very much!

[@davidscpa]

Your videos are very informative and I also appreciate the enhanced functionality and efficiency PivotPal provides me. I have an Excel question that is unrelated to this presentation. The rotating band that usually appears then the copy commend is initiated does not appear. The broken (dash) lines do appear but are static. This is a very recent manifestation and does not affect functionality. Would you have any suggestions how this irritating situation can be resolved?

[@manojdaruka]

Great video. Just to add to this topic Once u have highlighted the rows in yellow, how do you create a pivot table related to those specific yellow color-coded rows? Kindly advise.

[@Bradrackas]

Can you perhaps show this partial matching version of the row formatting but integrated with the Excel addin Fuzzylookup tool for the partial match?

[@ExcelCampus]

Great question! We did add a section to the blog post that uses the SEARCH function for partial matches. Here is a link to the post. www.excelcampus.com/tips/conditional-formatting-list-partial-match/ That is still going to be an exact match with a "contains" type search. We'll see about applying this to fuzzy match and add it to the list for future videos. Thanks again and have a nice day! 🙂

[@davevoisine6458]

funny, Just today I had a project very similar to your presentation. I used Vlookup and Left function. In my case i wanted to Isolate the rows that were true (delete the others) and only way i got that to work was a little basic VBA to copy rows that were true to another sheet. thanks!

[@msmith3090]

The Filter function has helped me a lot for situations like you describe. Only available in 365.

[@muskansaraf4774]

thank you so much

[@ExcelCampus]

Welcome 😊

[@bobnicholson6350]

Sir: enjoy your videos greatly...problem I have numbers in a column and want to rank them (first 4 numbers) according to largest 2nd largest and so on down...but I also want to have the first number colored green second one blue and so for the first largest numbers...Any help ...pls advise...Thanks Bob

[@336kp]

Thanks Jon. I've tried to use conditional formatting over a pivot table and some part of the pivot table is not able to apply the rule to. Please help

[@moesadr3342]

What is we only have one column and need to highlight those with the first 5 matching characters?

[@gajabathiarjuntg2390]

Thanks Jon. Which screen recorder are you using?

[@ExcelCampus]

Thanks Gajabathiarjun! I use Camtasia.

[@gajabathiarjuntg2390]

@@ExcelCampus hmm ok Thanks Jon

[@annmendoza3004]

Hi how do u apply this to a txt string where the length is not always equal

[@krishnamurthyalwala7187]

What shall I do if text contains some words instead of begins with

[@saifsalomy7507]

Can we use index match formula?

[@silviazelaya8773]

Countif has a limit of characters that it can look up, 245 I think. It will not work when the cell has more characters. What can I use to look for longer text?

[@paulcelmare6926]

Is there a way to highlight differences or similarities? For example, in column A and B are very similar data, but still not 100% the same. And now, is Excel able to highlight differences/similarities in cells from the same row (so different columns)? Let's say in A1 the text is "I like apples." and n B1 is "I don't like grapes.". So if similarities then in both cells following words are highlighted "I like." and if differences then "don't apples grapes" are highlighted.

[@philipmikheikin]

off topic question. is there a way to create incel dropping suggestion that shrinks down as you type, like a google search. suggestion comes from another workbook. i axplane in book1 i have dinamic table with column "item" with list of items in book2 i have dinamic table with column "item" both books are in onedrive in same folder, but book2 changes its location after ´save as´. but stil in onedrive so, when in book2 from its new location you are typing item "abc" you want to have a dropping down suggestionlist from all items that have "abc" in its name in book1. and this list gets narrower and narrower as you keep typing. thanks for any help. if there is any souse where i have to look for, will appretiate any info.

[@CreativelyChallenged]

Is there a way to remove (-) from phone numbers or a way to do conditional format if one sheet has numbers with dashes 910-555-1234 and the other sheet had no dashes 9105551234, is there a way to do duplicate values??

[@gondalaprasad]

Hi, I have a names column data range A2:A10, Few names Sai Rajesh, Rajesh Goud, Naresh etc. I want to highlight if names are partially matching, in this example Rajesh is matching two times hence Sai Rajesh and Rajesh Goud cells to be highlighted. Its very urgent. Please suggest.

[@niezelmhaealvaran9780]

Sample using index

[@Rkeev1]

People need to know that ALL cond format are formulas, meaning they slow down your sheet so don’t format the whole column.

[@saifsalomy7507]

Can we use index match formula?