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

[klp325]

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.
[TABLE="width: 148"]
<tbody>[TR]
[TD="align: right"]90802[/TD]
[TD="align: right"]90804[/TD]
[/TR]
[TR]
[TD="align: right"]90806[/TD]
[TD="align: right"]90808[/TD]
[/TR]
[TR]
[TD="align: right"]90813[/TD]
[TD="align: right"]90815[/TD]
[/TR]
[TR]
[TD="align: right"]91708[/TD]
[TD="align: right"]91711[/TD]
[/TR]
[TR]
[TD="align: right"]91723[/TD]
[TD="align: right"]91724[/TD]
[/TR]
[TR]
[TD="align: right"]91739[/TD]
[TD="align: right"]91741[/TD]
[/TR]
[TR]
[TD="align: right"]91761[/TD]
[TD="align: right"]91768[/TD]
[/TR]
[TR]
[TD="align: right"]91791[/TD]
[TD="align: right"]91792[/TD]
[/TR]
[TR]
[TD="align: right"]91901[/TD]
[TD="align: right"]91903[/TD]
[/TR]
[TR]
[TD="align: right"]91905[/TD]
[TD="align: right"]91906[/TD]
[/TR]
[TR]
[TD="align: right"]91908[/TD]
[TD="align: right"]91917[/TD]
[/TR]
[TR]
[TD="align: right"]91931[/TD]
[TD="align: right"]91935[/TD]
[/TR]
[TR]
[TD="align: right"]91941[/TD]
[TD="align: right"]91948[/TD]
[/TR]
[TR]
[TD="align: right"]91950[/TD]
[TD="align: right"]91951[/TD]
[/TR]
[TR]
[TD="align: right"]91962[/TD]
[TD="align: right"]91963[/TD]
[/TR]
[TR]
[TD="align: right"]91976[/TD]
[TD="align: right"]91980[/TD]
[/TR]
[TR]
[TD="align: right"]92003[/TD]
[TD="align: right"]92004[/TD]
[/TR]
[TR]
[TD="align: right"]92007[/TD]
[TD="align: right"]92011[/TD]
[/TR]
[TR]
[TD="align: right"]92013[/TD]
[TD="align: right"]92014[/TD]
[/TR]
[TR]
[TD="align: right"]92018[/TD]
[TD="align: right"]92030[/TD]
[/TR]
[TR]
[TD="align: right"]92036[/TD]
[TD="align: right"]92040[/TD]
[/TR]
[TR]
[TD="align: right"]92051[/TD]
[TD="align: right"]92052[/TD]
[/TR]
[TR]
[TD="align: right"]92054[/TD]
[TD="align: right"]92061[/TD]
[/TR]
[TR]
[TD="align: right"]92064[/TD]
[TD="align: right"]92072[/TD]
[/TR]
[TR]
[TD="align: right"]92074[/TD]
[TD="align: right"]92075[/TD]
[/TR]
[TR]
[TD="align: right"]92078[/TD]
[TD="align: right"]92079[/TD]
[/TR]
[TR]
[TD="align: right"]92081[/TD]
[TD="align: right"]92086[/TD]
[/TR]
[TR]
[TD="align: right"]92090[/TD]
[TD="align: right"]92093[/TD]
[/TR]
[TR]
[TD="align: right"]92101[/TD]
[TD="align: right"]92124[/TD]
[/TR]
[TR]
[TD="align: right"]92126[/TD]
[TD="align: right"]92140[/TD]
[/TR]
[TR]
[TD="align: right"]92142[/TD]
[TD="align: right"]92143[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Hi,

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

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

 

[klp325]

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)

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]

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]

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]

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]

Here is a link to my file that is having problems.https://signzoneinc-my.sharepoint.c...BCpM98vXSJYnEBeR-zuCSYBzYGpsyZXQyrVQ?e=rWhJLH

All of the zipcodes i am putting in here have 5 digits, however the output on sheet2 for many of the values is 7. I have a len formula in column B to illustrate.

There is a value from the error that says rownum2=1048577

 

[klp325]

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]

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.

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

 

[marcod953]

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:

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.