Formula for matching addresses
 

Hi There,


Can someone help with a formula in excel to find matching addresses between
2 columns? I want to be able to find matching addresses despite the spacing,
upper/lower case formatting and punctuation used in the addresses. Is there a
formula that can help me pick out matching addresses from 2 lists? If there
is, please help me? Email me at ..... Thanks,
Sean

Formula for matching addresses
 

Put this alongside the looked up value and copy down

=ISNUMBER(MATCH(TRIM(B2),TRIM($A$2:$A$20),0))

It is an arry formula, so commit with Ctrl-Shifte-Enter, not just Enter.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SeanUK2005" wrote in message
...
Hi There,


Can someone help with a formula in excel to find matching addresses
between
2 columns? I want to be able to find matching addresses despite the
spacing,
upper/lower case formatting and punctuation used in the addresses. Is
there a
formula that can help me pick out matching addresses from 2 lists? If
there
is, please help me? Email me at .....
Thanks,
Sean

Formula for matching Postal addresses
 

Hi Bob,


Thanks for the help. Either Im executing the formula incorrectly or it
doesnt work. I have two list lists of Postal Addresses. I need to extract the
ones that match but cant seem to find a proper formula as the Postal
Addresses arent in the same format. In other words, some are in Upper and
some lower case and some have 1 or more spaces inbetween and some have
punctuation eg in list 1 i have 23 Jones Lane and in list 2, I have 23
joneS Lane. This is a match but no formula will pick it out unless they are
identically spaced and the upper and lower case all match. Can you help? I
just need to pick out the common postal addresses in two lists so we can use
the information. Please help if u can? Thanks alot for the help. Cheers, Sean
"SeanUK2005" wrote:

Hi There,


Can someone help with a formula in excel to find matching addresses between
2 columns? I want to be able to find matching addresses despite the spacing,
upper/lower case formatting and punctuation used in the addresses. Is there a
formula that can help me pick out matching addresses from 2 lists? If there
is, please help me? Email me at ..... Thanks,
Sean

Formula for matching Postal addresses
 

Sean, the case is irrelevant in that formula, believe me.

Spaces are not so, but TRIM should manage that.

I put 23 Jones Lane in A9

I put 23 joneS Lane in B3

and this formula =ISNUMBER(MATCH(TRIM(B3),TRIM($A$2:$A$20),0)) returned
TRUE. I even added some extra spaces in it, no problem.

Remember, it is an array formula, so commit with Ctrl-Shift-Enter, not just
Enter.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SeanUK2005" wrote in message
...

Hi Bob,


Thanks for the help. Either Im executing the formula incorrectly or it
doesnt work. I have two list lists of Postal Addresses. I need to extract
the
ones that match but cant seem to find a proper formula as the Postal
Addresses arent in the same format. In other words, some are in Upper and
some lower case and some have 1 or more spaces inbetween and some have
punctuation eg in list 1 i have 23 Jones Lane and in list 2, I have 23
joneS Lane. This is a match but no formula will pick it out unless they
are
identically spaced and the upper and lower case all match. Can you help? I
just need to pick out the common postal addresses in two lists so we can
use
the information. Please help if u can? Thanks alot for the help. Cheers,
Sean
"SeanUK2005" wrote:

Hi There,


Can someone help with a formula in excel to find matching addresses
between
2 columns? I want to be able to find matching addresses despite the
spacing,
upper/lower case formatting and punctuation used in the addresses. Is
there a
formula that can help me pick out matching addresses from 2 lists? If
there
is, please help me? Email me at .....
Thanks,
Sean