Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
On Sat, 23 Aug 2008 14:00:00 -0700, 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, 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
"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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
Thanks Lars,
It worked well. :) Regards, "Lars-Ã…ke Aspelin" wrote: On Sat, 23 Aug 2008 14:00:00 -0700, 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, 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing/Matching two columns
shress wrote:
"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 Then I think Lars has a solution. O, I see you got it. Well done! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing & Stating The Nearest Matching | Excel Discussion (Misc queries) | |||
matching/comparing lists | Excel Worksheet Functions | |||
Comparing two columns and finding matching names | Excel Worksheet Functions | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |