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 -> New Rule -> Use a formula menu option.
What kind of formula could I enter into that bar which says roughly, "A substring of the value in this cell is also a substring of the value of another cell, anywhere in this document"?
Or anything to similar effect?
2 Replies
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.
- there is no such simple solution and without more restraints i couldn't even start to propose a more complex solution. By your 'definition' it could match a single character to any other cell with that character in it.
