Find Similar

[Guest]

I am currently using the following formula:
=IF(COUNTIF(E:E,E5)>1,"Duplicate","")
Basically it is entering Duplicate in a cell if an address appears twice. My
problem is this, our HR department has changed there reports procdure. Now
one address will read 109 E. 9th St. The other address will read 109 E 9th
St. They are the same address, just missing a period. It is not being flagged
as a duplicate entry.

Is there away to look for similarities?

Thanks in advance for your time.
Steve

 

[Bernard Liengme]

=SUMPRODUCT(--(SUBSTITUTE(E1:E1000,".","")=SUBSTITUTE(E1,".","")))
do not try using full columns ( as in E:E) with SUMPRODUCT
best wishes

 

[Guest]

Bernard,

As far as I can tell the formula works great. I am just not sure what it is
telling me. I am getting several different solutions to the formula. Can you
translate?

Thanks,
Steve

 

[Bernard Liengme]

Let A1 hold: "this . is a dot" - no quotes, of course
Let B1 hold formula =SUBSTITUTE(A1,".","")
B1 will display "this is a dot" --- i.e. the period is removed
any help?