Hi,

I am stuck with a problem. I need to compare two columns. one with ZIP and
other with street names. A Single ZIP has numerous streent names. Is there a
function which will say if the address is Right or Wrong?
for example:
11000 Apple Street <Right>
11000 Banana Street <Right>
11000 Mango Street <Wrong>

and so on

Thanks,

How does the function determine if it's right or wrong?
--
HTH,
Barb Reinhardt



"shress" wrote:

> Hi,
>
> I am stuck with a problem. I need to compare two columns. one with ZIP and
> other with street names. A Single ZIP has numerous streent names. Is there a
> function which will say if the address is Right or Wrong?
> for example:
> 11000 Apple Street <Right>
> 11000 Banana Street <Right>
> 11000 Mango Street <Wrong>
>
> and so on
>
> Thanks,

On Sat, 23 Aug 2008 14:00:00 -0700, shress
<(E-Mail Removed)> wrote:

>Hi,
>
>I am stuck with a problem. I need to compare two columns. one with ZIP and
>other with street names. A Single ZIP has numerous streent names. Is there a
>function which will say if the address is Right or Wrong?
>for example:
>11000 Apple Street <Right>
>11000 Banana Street <Right>
>11000 Mango Street <Wrong>
>
>and so on
>
>Thanks,

Try this formula in cell C1:

=IF(SUMPRODUCT((Sheet2!A$1:A$1000=A1)*(Sheet2!B$1:B$1000=B1))=0,"<Wrong>","<Right>")

Sheet2 is the name of the sheet where you put all possible ZIP codes
in column A and the correspoonding Street names in column B.
Increase the 1000 if you data takes more than 1000 rows.

Hope this helps / Lars-Åke

"smartin" wrote:

> shress wrote:
> > Hi,
> >
> > I am stuck with a problem. I need to compare two columns. one with ZIP and
> > other with street names. A Single ZIP has numerous streent names. Is there a
> > function which will say if the address is Right or Wrong?
> > for example:
> > 11000 Apple Street <Right>
> > 11000 Banana Street <Right>
> > 11000 Mango Street <Wrong>
> >
> > and so on
> >
> > Thanks,
>
> Do you have a table somewhere with valid zip/street combinations?
>
Hi, I have one table with valid zip/street combinations.
And I have got another bigger table which contails both valid and invalid
zip/street combinations and also lots of repetitions. I am trying to find out
those invalid addresses based on the correct table.

Thanks

Thanks Lars,

It worked well.

Regards,

"Lars-Ã…ke Aspelin" wrote:

> On Sat, 23 Aug 2008 14:00:00 -0700, shress
> <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >I am stuck with a problem. I need to compare two columns. one with ZIP and
> >other with street names. A Single ZIP has numerous streent names. Is there a
> >function which will say if the address is Right or Wrong?
> >for example:
> >11000 Apple Street <Right>
> >11000 Banana Street <Right>
> >11000 Mango Street <Wrong>
> >
> >and so on
> >
> >Thanks,
>
> Try this formula in cell C1:
>
> =IF(SUMPRODUCT((Sheet2!A$1:A$1000=A1)*(Sheet2!B$1:B$1000=B1))=0,"<Wrong>","<Right>")
>
> Sheet2 is the name of the sheet where you put all possible ZIP codes
> in column A and the correspoonding Street names in column B.
> Increase the 1000 if you data takes more than 1000 rows.
>
> Hope this helps / Lars-Ã…ke
>
>