Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Oct 07, 2022

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

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

excel
Formulas and Functions

7 Replies

Sort By
      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 10, 2022
        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.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 07, 2022

    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) 

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Oct 07, 2022

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

       

       

       

      I want it the other way from please

       

      Thanks

      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 07, 2022

        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)))

Resources