Determine missing zip codes from a range for sales territories and determine valid zip for state.

klp325

New Member
Joined
Sep 24, 2019
Messages
5
Originally our sales territories were set up with zip code ranges. Our new requirements are to have each zip code listed, based on a limitation on the new version of the territory tool we use.

So I need to take our existing range lists and convert them, without duplicates, to list each zip code. Each zip code must be a valid zip as well. So far I haven't found a non manual way to do this and I am hoping for your help.

Here is a very small sample of my current data. In the first row below, I need it to spit out 90803 as the missing value. I would also need to know if there are any zip codes between 90804 and 90806.
9080290804
9080690808
9081390815
9170891711
9172391724
9173991741
9176191768
9179191792
9190191903
9190591906
9190891917
9193191935
9194191948
9195091951
9196291963
9197691980
9200392004
9200792011
9201392014
9201892030
9203692040
9205192052
9205492061
9206492072
9207492075
9207892079
9208192086
9209092093
9210192124
9212692140
9214292143

<tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Hi,

Add the postcodes in columns A and B in Sheet1 (without headers), then add Sheet2 (or rename the sheets in the below)

Code:
Sub listzips()


Dim rownum As Long
Dim rownum2 As Long
Dim pcfrom As Long
Dim pcto As Long
Dim pc As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet


Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
rownum = 1
rownum2 = 1


Do Until sh1.Cells(rownum, 1).Value = ""


    pcfrom = sh1.Cells(rownum, 1)
    pcto = sh1.Cells(rownum, 2)
    
    Do Until pcfrom = pcto + 1
    sh2.Cells(rownum2, 1).Value = pcfrom
    pcfrom = pcfrom + 1
    rownum2 = rownum2 + 1
    Loop
    
rownum = rownum + 1
Loop


End Sub
 
Last edited:

klp325

New Member
Joined
Sep 24, 2019
Messages
5
Thanks for the reply. Can you please explain where this code goes or how to use it?



Hi,

Add the postcodes in columns A and B in Sheet1 (without headers), then add Sheet2 (or rename the sheets in the below)

Code:
Sub listzips()


Dim rownum As Long
Dim rownum2 As Long
Dim pcfrom As Long
Dim pcto As Long
Dim pc As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet


Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
rownum = 1
rownum2 = 1


Do Until sh1.Cells(rownum, 1).Value = ""


    pcfrom = sh1.Cells(rownum, 1)
    pcto = sh1.Cells(rownum, 2)
    
    Do Until pcfrom = pcto + 1
    sh2.Cells(rownum2, 1).Value = pcfrom
    pcfrom = pcfrom + 1
    rownum2 = rownum2 + 1
    Loop
    
rownum = rownum + 1
Loop


End Sub
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
It's VBA.

Go to File > Options > Customise Ribbon. And click the Developer box on the right and OK.

Then press Alt+F11 to open the Visual Basic Editor.

You'l;l see your open files on the left, right click on the one you are using and select Insert > Module.

Paste the code in the window that pops up and press the play button up top to run it.
 

klp325

New Member
Joined
Sep 24, 2019
Messages
5

ADVERTISEMENT

Thank you. I am getting a runtime error. The debugger highlights this section. sh2.Cells(rownum2, 1).Value = pcfrom

The error says 1004, application defined or object defined error. I cant attach a file or i would put the screen shot in. I also noticed that the zip codes in some weird case are coming out with 6 digits. Not all of them, but some.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
And sh2 is named Sheet2?

When the error occurs, hover the mouse over sh2, rownum2 and pcfrom and see if there are values in there.

Best bet to share the file is use something like dropbox where you upload the file and it generates a link. There's plenty of free file sharing sites around.
 

klp325

New Member
Joined
Sep 24, 2019
Messages
5
Also, it is calculating values outside of the final value. That is how we are ending up with so large values. The list I linked should be for Texas and surrounding area zips. I see that the list the excel file made goes way farther than that.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Late reply, sorry.

Row 433 you had from 79227 to 78232. So it didn't know when to stop as the to is less than the from.

Below just ignores that row, you may want a different solution. Or just check and fix the data first.

Code:
Sub listzips()




Dim rownum As Long
Dim rownum2 As Long
Dim pcfrom As Long
Dim pcto As Long
Dim pc As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet




Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
rownum = 1
rownum2 = 1




Do Until sh1.Cells(rownum, 1).Value = ""




    pcfrom = sh1.Cells(rownum, 1)
    pcto = sh1.Cells(rownum, 2)
    
    If pcto < pcfrom Then
    Else
    Do Until pcfrom = pcto + 1
    sh2.Cells(rownum2, 1).Value = pcfrom
    pcfrom = pcfrom + 1
    rownum2 = rownum2 + 1
    Loop
    End If
    
rownum = rownum + 1
Loop




End Sub
 
Last edited:

marcod953

New Member
Joined
Jul 1, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm new to this forum and not sure if I'm doing this correctly but I have a similar task to complete as klp235. The only difference is that I have territory assignments to ranges of zip codes. Here is a small example of what I have. I need to get the individual zip codes within the range and have it named the territory name. Example:

1593635053312.png


I'd like the results on a separate sheet.

Any help would be greatly appreciated. If I'm not posting this in the correct place, please let me know so I can do this correctly.
 

Forum statistics

Threads
1,148,370
Messages
5,746,303
Members
424,006
Latest member
Metal_warrior

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
Top