Find Similar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 23 Apr 2007 16:28 GMT

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

Reply to this Message

Bernard Liengme - 23 Apr 2007 17:52 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I am currently using the following formula:
> =IF(COUNTIF(E:E,E5)>1,"Duplicate","")
[quoted text clipped - 10 lines]
> Thanks in advance for your time.
> Steve

Reply to this Message

Steve - 24 Apr 2007 12:28 GMT

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

> =SUMPRODUCT(--(SUBSTITUTE(E1:E1000,".","")=SUBSTITUTE(E1,".","")))
> do not try using full columns ( as in E:E) with SUMPRODUCT
[quoted text clipped - 13 lines]
> > Thanks in advance for your time.
> > Steve

Reply to this Message

Bernard Liengme - 24 Apr 2007 15:56 GMT

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?

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Bernard,
>
[quoted text clipped - 27 lines]
>> > Thanks in advance for your time.
>> > Steve

Reply to this Message