Conditional Format Same Words With a Twist

[vill]

Hi guys, this might be something of unrealistic one, but giving it a chance just in case you might be aware of how...

1. Do you know if there's a way to conditional format same words in cells BUT with a twist where say one word is Achieving and another is Achiever OR one word might be mispelled, but they all have the same meaning, to format them still?

2. Also, on even more complicated note: would there be a possibility to format words that are found no less than let's say 3 times?

If 2nd options is too complicated or not possible, then solving at least the 1st one would be extremely great help.

Attaching Excel file and screenshot below. Thanking you in advance!

https://www.excelforum.com/attachmen...1&d=1559984598 [EXCEL]

2019-06-08_1155.png

[Pete_UK]

Take a look at the attached file.

I've assumed that the first column of words are the ones you want to look for a partial match with, and I've used this CF formula to achieve that for the other columns:

=COUNTIF($C$42:$C$45,"*"&LEFT(G42,LEN(G42)-2)&"*")

It basically removes the last two letters from the word to look for a partial match, and applies this to the cells in the range $G$42:$U$59. I've coloured them purple as well as bold, to help them stand out.

Hope this helps.

Pete

[vill]

Hi Pete, wow.... this is magical! Blown away....

I had an idea that a mach should be between text in all columns, not just from a first column. Looking at your formula (very advanced) I'm wondering could there be a way to make it 5 times the same for a match to be done from all 5 columns?

So one part of a formula could be $C$42:$C$59, another part $G$42:$G$59, another part $K$42:$K$59, another part $O$42:$O$59 and last part $S$42:$S$59 ?

Thank you so much for your help. Amazing.

[Pete_UK]

Select cell G42 and click on Conditional Formatting | Manage rules. In the pop-up that appears you should change the "Applies to" box to the range =$C$42:$U$59, and then click on Edit Rule.

In this dialogue box you should change the formula to this:

=COUNTIF($C$42:$U$59,"*"&LEFT(C42,LEN(C42)-2)&"*")>1

(changes shown in red), and then you can click OK to exit the dialogue box.

Note that the work Calm is highlighted - this is because the letters Ca from that word are matching with the words Analytical, Logical and Dedicated from other columns, which is one of the drawbacks of partial matching.

Hope this helps.

Pete

[vill]

Wow wow wow.... Magician :-) Thank you, sir!!!!!!!!! So much appreciated, thank you.

[Pete_UK]

You're welcome - thanks for the rep.

Pete