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

K

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.
[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]
 
Sort by date Sort by votes
Hi, try this tweak

VBA 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
Dim territory As String

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

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

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

End Sub
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
mrshl9898 said:
Hi, try this tweak

VBA 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
Dim territory As String

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

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

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

End Sub
Click to expand...



Thanks for the quick response but I must be doing something wrong. I get the following error.
1593707220991.png
 
Upvote 0
Are your sheets called "Sheet1" and Sheet2"? Rename these to whatever your sheets are called. Sheet1 is the Data, 2 the output

VBA Code: 
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
 
Upvote 0
Yes, the sheets are named exactly "Sheet1" & "Sheet2" with capital S and no space between the word and numbers. (No parenthesis)
 
Upvote 0
is that what you want?

TeritoryFromToTeritoryFilledZips
Teritory0019080290804Teritory00190802
Teritory0029080690808Teritory00190803
Teritory0039081390815Teritory00190804
Teritory0019170891711Teritory00290806
Teritory0039172391724Teritory00290807
Teritory0069173991741Teritory00290808
Teritory0079176191768Teritory00390813
Teritory0089179191792Teritory00390814
Teritory0099190191903Teritory00390815
Teritory0099190591906Teritory00191708
Teritory0099190891917Teritory00191709
Teritory0099193191935Teritory00191710
Teritory0139194191948Teritory00191711
Teritory0149195091951Teritory00391723
Teritory0159196291963Teritory00391724
Teritory0169197691980Teritory00691739
Teritory0169200392004Teritory00691740
Teritory0169200792011Teritory00691741
Teritory0069201392014Teritory00791761
Teritory0079201892030Teritory00791762
Teritory0089203692040Teritory00791763
Teritory0229205192052Teritory00791764
Teritory0239205492061Teritory00791765
Teritory0249206492072Teritory00791766
Teritory0259207492075Teritory00791767
Teritory0229207892079Teritory00791768
Teritory0239208192086Teritory00891791
Teritory0249209092093Teritory00891792
Teritory0259210192124Teritory00991901
Teritory0309212692140Teritory00991902
Teritory0319214292143Teritory00991903
Teritory00991905
Teritory00991906
Teritory00991908
Teritory00991909
Teritory00991910
Teritory00991911
Teritory00991912
Teritory00991913
Teritory00991914
Teritory00991915
Teritory00991916
Teritory00991917
Teritory00991931
Teritory00991932
Teritory00991933
Teritory00991934
Teritory00991935
Teritory01391941
Teritory01391942
Teritory01391943
Teritory01391944
Teritory01391945
Teritory01391946
Teritory01391947
Teritory01391948
Teritory01491950
Teritory01491951
Teritory01591962
Teritory01591963
Teritory01691976
Teritory01691977
Teritory01691978
Teritory01691979
Teritory01691980
Teritory01692003
Teritory01692004
Teritory01692007
Teritory01692008
Teritory01692009
Teritory01692010
Teritory01692011
Teritory00692013
Teritory00692014
Teritory00792018
Teritory00792019
Teritory00792020
Teritory00792021
Teritory00792022
Teritory00792023
Teritory00792024
Teritory00792025
Teritory00792026
Teritory00792027
Teritory00792028
Teritory00792029
Teritory00792030
Teritory00892036
Teritory00892037
Teritory00892038
Teritory00892039
Teritory00892040
Teritory02292051
Teritory02292052
Teritory02392054
Teritory02392055
Teritory02392056
Teritory02392057
Teritory02392058
Teritory02392059
Teritory02392060
Teritory02392061
Teritory02492064
Teritory02492065
Teritory02492066
Teritory02492067
Teritory02492068
Teritory02492069
Teritory02492070
Teritory02492071
Teritory02492072
Teritory02592074
Teritory02592075
Teritory02292078
Teritory02292079
Teritory02392081
Teritory02392082
Teritory02392083
Teritory02392084
Teritory02392085
Teritory02392086
Teritory02492090
Teritory02492091
Teritory02492092
Teritory02492093
Teritory02592101
Teritory02592102
Teritory02592103
Teritory02592104
Teritory02592105
Teritory02592106
Teritory02592107
Teritory02592108
Teritory02592109
Teritory02592110
Teritory02592111
Teritory02592112
Teritory02592113
Teritory02592114
Teritory02592115
Teritory02592116
Teritory02592117
Teritory02592118
Teritory02592119
Teritory02592120
Teritory02592121
Teritory02592122
Teritory02592123
Teritory02592124
Teritory03092126
Teritory03092127
Teritory03092128
Teritory03092129
Teritory03092130
Teritory03092131
Teritory03092132
Teritory03092133
Teritory03092134
Teritory03092135
Teritory03092136
Teritory03092137
Teritory03092138
Teritory03092139
Teritory03092140
Teritory03192142
Teritory03192143
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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