Forum Discussion
Simple way to highlight near-duplicates
I would like to write a simple formula to highlight cells that are near-duplicates rather than exact duplicates. I would like to try this by entering a formula into the Conditional Formatting -> ...
I don't know a simple way for matching substrings but i can suggest formula / rule for conditional formatting if you want e. g. the left 2 words of a string to be your substring:
=COUNT(FIND(LEFT(E3,FIND(" ",E3,FIND(" ",E3)+1)-1),$A$3:$A$7))
Substring of 2 words is taken from cell E3 if you enter formula:
=LEFT(E3,FIND(" ",E3,FIND(" ",E3)+1)-1)
in H3 for example.
In my example data which should be formatted is in range E3:E6 and strings to be compared with substring from range E3:E6 are in range $A$3:$A$7.
Please compare with the attached file.