Return value after searching 3 col's of zipcodes

**Oscar Fox** · 05-11-2011, 06:45 AM

Hi everyone, I know the basics but this is beyond me.

I run a small courier Co and charge a fixed rate between towns in set zones, I have 3 columns of Zip-codes (Postcodes here in Oz) corresponding to the zones and wish to input two codes and return a dollar value based on what zone they are in.

My criteria is for example:
Any two codes from zone one is $20.00
Any two codes from zone two is $22.50
Any two codes from zone three is $25.00
Any one code from zone one and either of the other zones is the $ value of the higher zone plus $8.00 and the same for between zones two and three (the higher value plus $8.00)

I hope this makes sense and is do-able.

Oscar

**Domski** · 05-11-2011, 06:51 AM

Hi,

I think you would probably be better off having one column of ZIP codes and the zone that the code relates to in the column next to it.

You can then do a lookup for both your zip codes on that one table and use a logical function to determine the cost based on the zones that are returned.

Dom

**Marcol** · 05-11-2011, 06:53 AM

Hi Oscar Fox

Welcome to the forum

This is doable, but so much easier to check if you provide a workbook with the three relevant columns.

It should not contain any sensitive data.

Cheers

From Forum FAQs

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

**Domski** · 05-11-2011, 07:05 AM

I put together an example if it helps.

Dom

**snb** · 05-11-2011, 07:13 AM

3 columns containing zipcodes: A:C

first zipcode: E1
second zipcode E2
tariffresult in E3

Please Login or Register  to view this content.

**Oscar Fox** · 05-11-2011, 08:20 AM

Man you guy's are great (and fast)

Domski, I like the ease of the single column. Would it be easy to add a second column of data corresponding to the zipcode that would print out the town name when the zipcode is entered.
Sorta gives a reassuring confirmation that the right towns zip was entered.

Anyway thanks for the instant and brilliant help.
Oscar

**Domski** · 05-11-2011, 08:35 AM

Hi Oscar,

Sure, see the revised example attached.

Dom

**Oscar Fox** · 05-13-2011, 03:37 AM

Originally Posted by Domski

Hi Oscar,

Sure, see the revised example attached.

Dom

I had a good play with your code and found it did most of what I wanted nice and cleanly, BUT..... I hit a snag when I realized that some of my zips were in two or more adjacent zones where the zone boundary overlaps and wouldn't return the correct pricing. I think this was why I was originally playing with 3 columns of data not a single one. I sat back down and wrote a new condition table.

Zones are A, B, C, all else D
Any two zips in: A=$20 B=$25 C=$30
One zip in each: AB=$28 AC=$32 BC=$35 except where they appear wholly in A,B or C
Any other combination returns"ask for pricing"

Each Zone corresponds to a column of zip data and some data may appear in more than one column.

My brain is now officially mush and no amount of code manipulation seems obvious to me, please HELP........