Find Similar
 

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

Find Similar
 

=SUMPRODUCT(--(SUBSTITUTE(E1:E1000,".","")=SUBSTITUTE(E1,".","") ))
do not try using full columns ( as in E:E) with SUMPRODUCT
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve" wrote in message
...
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

Find Similar
 

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" wrote:

=SUMPRODUCT(--(SUBSTITUTE(E1:E1000,".","")=SUBSTITUTE(E1,".","") ))
do not try using full columns ( as in E:E) with SUMPRODUCT
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve" wrote in message
...
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

Find Similar
 

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve" wrote in message
...
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" wrote:

=SUMPRODUCT(--(SUBSTITUTE(E1:E1000,".","")=SUBSTITUTE(E1,".","") ))
do not try using full columns ( as in E:E) with SUMPRODUCT
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve" wrote in message
...
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