How to highlight cells where there are 2 or more words the same (ie a name)

Contributor

Hi

 

If i had a cell with 

Miss Campbell and Miss Campbell

 

 

I want Excel to highlight that cell as I am checking the accuracy of the data it is pulling 

 

Thanks

Sarah

7 Replies

@SGeorgie  Highlight the cell range and select conditional formatting and select custom formula and use this:

=ROWS(TEXTSPLIT(A1,," "))>ROWS(UNIQUE(TEXTSPLIT(A1,," ")))

where A1 is replaced with the upper left most cell of the applied to range

(note this assumes you have Excel 365) 

@mtarler This kind of works but highlights those that are not containing the same name

 

SGeorgie_0-1665148657680.png

 

 

I want it the other way from please

 

Thanks

That doesn't seem right. What did you set the formatting to be? Are you sure the column isn't formatted a peach fill and the conditional formatting is changing it to white?
That all said there is a bigger issue here and that this formula looks for a duplication of ANY word in the text so Mr George and Mr Joe would be found true because "Mr" is repeated. The formula will get much more complicated if we have to exclude specific words like Mr, Mrs, Ms, and Miss and "and" itself if there might be a case of "Mr George and Mrs Sarah and Miss Sue" because the "and" is repeated

for example something like this includes a list of words/terms to exclude:

=LET(in,A1,
exclude,{"Mr","Mrs","Miss","Dr","Ms","Jr","Sr","and"},
wlist,TEXTSPLIT(in,," "),
fwlist,FILTER(wlist,NOT(ISNUMBER(MATCH(wlist,exclude,0)))),
ROWS(fwlist)>ROWS(UNIQUE(fwlist)))

@SGeorgie 

This might work:

=LET(name,LEFT($F1,FIND(" ",$F1)-1),COUNTIF($F1,name&"*"&name))

 

ar i set this formula with peach but yeah lots will be Mr ... and Mr ....
while other are Miss. and Miss..... and Miss..............

no worries, thank you
Is this searching specific names
my interpretation of patick's formula is that it is taking the FIRST word from the string and seeing if THAT word is in the whole string at least 2x. So with "Dave Smith and Sue Booker and Dave Moon" it would find "Dave" and then see if "Dave ... Dave" exists in the string and in this case would reply true because "Dave" is in there 2x.