Comparing postal/zip data in excel

[DC Outsourcing]

HI Folks

I am an excell novice truth be told but I have a query which I am seriously struggling with.

I have two sheets

Sheet 1 is a reference table with a list of postcodes/zipcodes - These postcodes are ones which qualify for a marketing campaign i am running.

Sheet 2 is raw data with around 6k postcodes that may or may not qualify.

On the sheet called reference the data is situated in column A

On the raw data sheet the full postcode list is also in column A

In an ideal world what I would like to achieve is a Column eg L2 in the raw data sheet that displayed either a Q for qulaifying or NQ for non qualifying so that we can succesfully sort the data.

Any help would be appreciated

[sourabhg98]

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data. Change names, prices etc.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

http://www.excelforum.com/members/da...ch-a-file.html

[DC Outsourcing]

I have attached a sample copy.

In the qualify column I have put what the desired results are. So in essence I need to cross reference the postcodes on the source sheet with the postcodes on the reference sheet. If the postcode on the source sheet matches ANY postcode on the reference sheet then "Q" should be displayed and if no match is found then "NQ" should be displayed.

Thanks again

[sourabhg98]

Hello
Type this one in K2
=IF(IFERROR(MATCH(A2,Reference!A:A,0),"NQ")="NQ","NQ","Q")
and copy it downwards

Hope this helps...

Ask for any other query you have..

[DC Outsourcing]

Excellent thank you so much. I really appreciate your help.

Any chance you can explain the logic behind that eg. what each part means so I can understand and hopefully learn from your help. thanks

[sourabhg98]

Ohk so let me explain
Match(a,b,c)
Here,
a=value you want to search
b= where you want to search
c=this is the type of search ...if you type a 0 here it means exact search i.e. match formula looks for exactly the same value
So in your case
a= A2 we need to search the value in A2
b=Reference!A:A ...we need to search in column A of reference sheet
c=0 for an exact match
So we get
=MATCH(A2,Reference!A:A,0)
So this will give the row number of your array where a match is found
and if no match is found it will return an error "N/A"

So we need an iferror formula
Iferror(,a,b)
a=value containing error
b=what we want if an error is there in "a"

in our case if we have an error in
=MATCH(A2,Reference!A:A,0)
we want to to get "NQ"
i.e.
a=MATCH(A2,Reference!A:A,0)
b="NQ"
so we get
=IFERROR(MATCH(A2,Reference!A:A,0),"NQ")
this would return the row number if value if found or if not found then "NQ"
but we dont need the row number...we need "Q" instead
So an if formula would work
=if(a,b,c)
a=condition we want to check
b=value if condition is true
c=value if condition is false

here we want to check if
=IFERROR(MATCH(A2,Reference!A:A,0),"NQ") is equal to "NQ" then give "NQ" else give "Q"

So
a=IFERROR(MATCH(A2,Reference!A:A,0),"NQ")="NQ"
b="NQ"
c="Q"

So finally we get
=IF(IFERROR(MATCH(A2,Reference!A:A,0),"NQ")="NQ","NQ","Q")

Hope it helps!!
If it helps then you can add to my reputation by clicking on add reputation below and then marking the thread as "SOLVED"