Trying to search for duplicates of company names in a single column. However, sometimes punctuation and spacing are different, so my formula does not always work. Please see my example below from my list of ~4k company names.
The formula used works for the first four company names listed — the result is that they are not duplicates and the word ‘DUPLICATE’ does not appear in the formula column. The formula also works for the last two listed — result is that they ARE duplicates and the word DUPLICATE appears in the formula column.
However, the formula does not work for the middle three company names listed (Dasher Dancer & Prancer PC). It does not identify them as duplicates, even though they are pretty much duplicates except for punctuation, spacing and caps.
What could I modify on my formula to make it work for those three names? Or what other function and/or formula would identify those three names as duplicates, or otherwise identify how similar they are? Am I asking the impossible?
Formula is: =IF(H557=558,“DUPLICATE”,"").
Here’s the example from my list:
Blitzen, C.P.A., P.C. Comet LLC Christen M. Cupid, CPA, CFP Dasher & Co. Dasher Dancer & Prancer, PC Dasher, Dancer & Prancer, P.C. DASHER, DANCER & PRANCER, PC Donner & Associates CPAs, PC Donner & Associates CPAs, PC