Finding Zip Codes within a Zone

umpd

New Member
Joined
Mar 8, 2010
Messages
28
I have a spreadsheet that has Zip Codes by Service Days, and then I have another Spreadsheet Zip Code Ranges and the Zone they fall in. I want find the Zip Code associated within that Zone for the Zip Code. Does anyone have a formual for figuring this out. I listed some data below.

Zip Code Spreadsheet

State Destination Zip Service Days
NY 12095 2


Zone Chart


From Zip </SPAN>To Zip</SPAN>Zones</SPAN>
004999999999</SPAN>004999999999</SPAN>
005999999999</SPAN>005999999999</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
046999999999</SPAN>046999999999</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
047999999999</SPAN>047999999999</SPAN>FEDEX06</SPAN>FEDEX06</SPAN>
129999999999</SPAN>129999999999</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
132999999999</SPAN>132999999999</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>
139999999999</SPAN>139999999999</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
179999999999</SPAN>179999999999</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>
199999999999</SPAN>199999999999</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
212999999999</SPAN>212999999999</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL><COL></COLGROUP>


<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>



</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>
</SPAN> </SPAN></SPAN></SPAN>
</SPAN></SPAN></SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I have a spreadsheet that has Zip Codes by Service Days, and then I have another Spreadsheet Zip Code Ranges and the Zone they fall in. I want find the Zip Code associated within that Zone for the Zip Code. Does anyone have a formual for figuring this out. I listed some data below.

Zip Code Spreadsheet

State Destination Zip Service Days
NY 12095 2

Zone Chart
From Zip </SPAN>
To Zip</SPAN>
Zones</SPAN>
004999999999</SPAN>
004999999999</SPAN>
005999999999</SPAN>
005999999999</SPAN>
FEDEX05</SPAN>
FEDEX05</SPAN>
046999999999</SPAN>
046999999999</SPAN>
FEDEX05</SPAN>
FEDEX05</SPAN>
047999999999</SPAN>
047999999999</SPAN>
FEDEX06</SPAN>
FEDEX06</SPAN>
129999999999</SPAN>
129999999999</SPAN>
FEDEX05</SPAN>
FEDEX05</SPAN>
132999999999</SPAN>
132999999999</SPAN>
FEDEX04</SPAN>
FEDEX04</SPAN>
139999999999</SPAN>
139999999999</SPAN>
FEDEX05</SPAN>
FEDEX05</SPAN>
179999999999</SPAN>
179999999999</SPAN>
FEDEX04</SPAN>
FEDEX04</SPAN>
199999999999</SPAN>
199999999999</SPAN>
FEDEX05</SPAN>
FEDEX05</SPAN>
212999999999</SPAN>
212999999999</SPAN>
FEDEX04</SPAN>
FEDEX04</SPAN>

<TBODY>
</TBODY>
Something seems wrong with your Zone Chart.. the From Zip and To Zip numbers are identical and each Zip Code has too many digits in them. Also, your Zones column seems to have two entries per line and they too are identical. Please explain why this is so or, if it is not what you really have, then please replace it with real data. Also, what column is the Zip Code in on the Zip Code Spreadsheet.
 
Upvote 0
Something seems wrong with your Zone Chart.. the From Zip and To Zip numbers are identical and each Zip Code has too many digits in them. Also, your Zones column seems to have two entries per line and they too are identical. Please explain why this is so or, if it is not what you really have, then please replace it with real data. Also, what column is the Zip Code in on the Zip Code Spreadsheet.

Sorry, I did a copy in Paste from another Spreadsheet and values didn't carry over. Here is the excact Spreasheet with the Zones. So

Destination Zip</SPAN>Postal Code From</SPAN>Postal Code Thru</SPAN> Ground Zone</SPAN>
Formula</SPAN>Formula</SPAN>GRD Zone for AK,HI</SPAN>JDE Ground Zone</SPAN>JDE Express Zone</SPAN>
000-004</SPAN>000</SPAN>004999999999</SPAN>NA</SPAN>
005</SPAN>005</SPAN>005999999999</SPAN>5</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
010-046</SPAN>010</SPAN>046999999999</SPAN>5</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
047</SPAN>047</SPAN>047999999999</SPAN>6</SPAN>FEDEX06</SPAN>FEDEX06</SPAN>
048-129</SPAN>048</SPAN>129999999999</SPAN>5</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
130-132</SPAN>130</SPAN>132999999999</SPAN>4</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>
133-139</SPAN>133</SPAN>139999999999</SPAN>5</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
140-179</SPAN>140</SPAN>179999999999</SPAN>4</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>
180-199</SPAN>180</SPAN>199999999999</SPAN>5</SPAN>FEDEX05</SPAN>FEDEX05</SPAN>
200-212</SPAN>200</SPAN>212999999999</SPAN>4</SPAN>FEDEX04</SPAN>FEDEX04</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>
</SPAN>
</SPAN> </SPAN>
</SPAN>
</SPAN>
</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>
 
Upvote 0
Follow up questions...

1. You revised chart seems to have a gap for Destination Zips between 006 and 009999999999... is that intentional or did you omit it by mistake?

2. I am guessing the Destination Zip is the first 3 numbers of a Zip Code, correct?

3. Your actual question ("I want find the Zip Code associated within that Zone for the Zip Code") seems to be worded incorrectly... as written, it sounds like you have the Zone and want to look up the Zip Code for it. Are you actually asking to be able to find the Zone for the Zip Code appearing on the Zip Code Spreadsheet? Or were you looking for the Destination Zip that covers the Zip Code from the Zip Code Spreadsheet? Or maybe you wanted something else? Please clarify this for us.

4. Are the actual names of your two worksheets (what I presume you meant by "spreadsheet") these... "Zip Code" and "Zone Chart"?

5. You did not answer my second question in my last posting... what column is the Zip Code in on the Zip Code Spreadsheet?
 
Upvote 0
Answer to you First Question, yest that is intenational.

Answer to your Second, Yes the first three number are the Destination Zip. Column A has the Actual Range 000-004, and have to concatenate the 99999's.

Answer to your Third question, In my fist spreadsheet, I want to know what zone that specific zip code falls under.

Answer to your fourth questions in the The First Spreadsheet is called Service Days and the Second Spreadsheet is just called a Zone Chart.

Answer to your Fifth question, The Data will be imported into our Transportation system, The Data will look like the following below when being imported.
Carrier Zone From Postal Code Thru Postal
PARZN002 28700
 
Upvote 0
Answer to you First Question, yest that is intenational.

Answer to your Second, Yes the first three number are the Destination Zip. Column A has the Actual Range 000-004, and have to concatenate the 99999's.

Answer to your Third question, In my fist spreadsheet, I want to know what zone that specific zip code falls under.

Answer to your fourth questions in the The First Spreadsheet is called Service Days and the Second Spreadsheet is just called a Zone Chart.

Answer to your Fifth question, The Data will be imported into our Transportation system, The Data will look like the following below when being imported.
Carrier Zone From Postal Code Thru Postal
PARZN002 28700
Actually, your answer to #5 was not what I asked for. For the data "NY 12095 2", I wanted to know what column the 12095 was in. I'll assume it is Column B (on the Service Days sheet). Given that, I think this formula will calculate the Zone for the Zip Code in that column...

=LOOKUP(0+LEFT('Service Days'!B2,LEN('Service Days'!B2)-2),'Zone Chart'!B$2:B$12,'Zone Chart'!D$2:D$12)

The two red highlighted numbers are the row numbers for the last piece of data on the Zone Charts (so you will need to adjust them as needed). The two purple B's are the assumed Column B that I mentioned above.
 
Upvote 0

Forum statistics

Threads
1,203,544
Messages
6,056,027
Members
444,840
Latest member
RazzelDazel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top