Oct 07 2022 05:20 AM
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
Oct 07 2022 05:34 AM
@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)
Oct 07 2022 06:18 AM
@mtarler This kind of works but highlights those that are not containing the same name
I want it the other way from please
Thanks
Oct 07 2022 06:41 AM - edited Oct 07 2022 06:50 AM
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)))
Oct 07 2022 06:56 AM
Oct 07 2022 06:59 AM
Oct 10 2022 08:26 AM
Oct 10 2022 10:36 AM