COUNTIF() help I do not know how to make it working

[MaxDp2020]

Hi, I need a formula.

Column "B" has "names + surnames" with even homonyms & duplicated people.

Column "M" contains ONLY SURNAMES that I need to highlight if present in column “B”.

I want all the cells of column B, containing the single txt WORDS of the cells of column M, to be formatted with
bold PURPLE colour font.

The search must be of the text, among the single words in B, not of entire cells.
Column B contains 10.000 people with duplicates of with the same surname or even homonymous names.


I have been suggested to use the COUNTIF() function, to check if a text entry is found in another column as in the COUNTIF() formula the key is to use "*"& the search cell and another &"*" wildcard to search column B.
So, they suggested to make a new “N” or “helper” column and then to write this in N:

=COUNTIF($B$2:$B$10,"*"&M2&"*")
This counts if any part of M is found in B.
Then they said to use a formula in the conditional formatting FORMULA:
=O2=1
and apply to column B with FILL = PURPLE

Unfortunately I do not know where and how to input the COUNTIF() formula in coloum N
and I do not know where and how insert the formatting formula =O2=1
and where and how to apply to column B with FILL = PURPLE.

Can you help? Thanks, Max

[Fluff13]

Hi & welcome to the board.
No need for a helper column.
Select B2:B10 > Conditional Formatting > New rule > Use a formula > Use
=COUNT(SEARCH($M$2:$M$10,B2))

Select your format > OK.

[MaxDp2020]

Thanks a lot!

I did try and, as many times before, I get this error :-(:

error.jpg

[Fluff13]

You will need to convert the formula to match you language.
If you update your profile to show what country you are in, then I can help with that. But having a location of "here" is of no help at all.

[MaxDp2020]

Thanks a lot, I inserted the country. I already translated using the file you sent:

=CONTA.NUMERI($M$2:$M$7056,B2))

The specific xls example file you sent me is working fine, thanks a lot. Anyway, when I insert the formula in my original large file, today I get the error , while yesterday night I had no errors, but anyway nothing happened (it was not working).

I checked your file and I see 3 different formulas:

=CONTA.NUMERI(RICERCA($M$2:$M$10;B2))

[only cells containing] $B$2:$B$7056

[Value between] ="B2+$B$2:$B$7056" and ="$B$2:$B$7056"

his-3-formulas.jpg

I do not know how to insert them...

When I select the 19.002 cells in B, and I use the formula you gave me:
=CONTA.NUMERI($M$2:$M$7056,B2))

together with the purple format. I am now getting the error again:

exempl.jpg

error2.jpg

It is strange because yesterday the very same translated formula at least was accepted even if it did not gave any results (there are many surnames that should become purple on B)

Sorry I am quite a basic user of Excell, thanks again for any hint..

Max

[Pepe Le Mokko]

Deleted this post.Error on my part

[MaxDp2020]

Thanks Pepe. Max

[Fluff13]

You have a comma in the formula, when I think it should be a semicolon.
=CONTA.NUMERI($M$2:$M$7056;B2))

[Pepe Le Mokko]

Sorry Max, didn't see the file as it was attached to an image

[MaxDp2020]

I did correct with the ";"

=CONTA.NUMERI($M$2:$M$7056;B2))

but I still get the error..

Could it be a bug in Excel?

error3.jpg

[AliGW]

Ditch the final bracket:

=CONTA.NUMERI($M$2:$M$7056;B2)

[Fluff13]

Hadn't noticed that the SEARCH function was missing, it should be
=CONTA.NUMERI(RICERCA($M$2:$M$10;B2))

[MaxDp2020]

Thanks! Now it is working! Anyway, the formula makes errors:

It does not mark all "Wallace" for example, only 5 or 6 of them,

and it marks us present surnames also "pieces of the complete txt string",

specially the names listed us: *Mary
(which sometimes have * prefix)

or

if it has to search and underline "Smith", it underlines also "Aerosmith" or "Smithson".. but this is quite natural and probably unavoidable.

The main problem is that it does NOT underline ALL the Smith in the 19.000 people list..

Any clue? Thanks again, great job! Really thanks.

Max

[Fluff13]

Can you supply a new sample file that shows these problems?

[MaxDp2020]

I believe the problem with the NOT marked (purple) surnames is because 19.002 are many and I am seeing them coming and going!
Unfortunately I cannot share the main file for their privacy but I think the problem is too much memory used so excel keeps changing.

Is there a way, after the formula, to sostitute the purple with, for example red, in order to unlink the marked ones from any formula ?

I do know the find and replace menu but it does not find and replace only the colour, it needs a txt.

[Fluff13]

I'm afraid I don't understand what you mean.

[MaxDp2020]

Sure, sorry. I try to express a little better:

To run a "find and replace" the excel function form need a txt or number.

It would be good to run a found "purple" and replace it with "red". But this seems impossible (to me).

In that way I would replace the purple formatting and just copy and paste the entire column, taking away our formatting formula once used.
(which for 19.000 entries, seems as always calculating and giving "visual" errors..maybe just for memory..)

I told before that for ex. the surname Wallace was not always in purple after applying the conditioned formula, but this is not totally right, if I scroll the screen or click near a white Wallace cell it get purple.. So it is a matter of memory...

[Fluff13]

You would have to do that with VBA, in which case you could just get rid of the CF.

[MaxDp2020]

I thank you very much for your help. Best regards. Max

[Fluff13]

You're welcome & thanks for the feedback