List of zip codes by org code into ranges

[jfm2143]

Hello,

This is my first time using this forum so please be gentle. Your time and help would be greatly appreciated.

I've built a list of all zip codes in the US with their corresponding organization code, of which there are ~100. A total of ~42k rows.

I now need to group the data in a format consisting of a range of zip codes in each org, with a starting zip code and ending zip code. These ranges need to skip any zip codes not listed. So if a New York org went; 12540, 12541, 12542, 12543... on to 12553 then skipped to 12555, then to 12563 and carried on 12564, 15565... to 12572 the ranges would be 12540-12553, 12555 - 12555, 12563 - 12572. Below is an example of my data set and the format I need it in.

What I have:
[TABLE="width: 345"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Zip Code[/TD]
[TD]State[/TD]
[TD]Org[/TD]
[/TR]
[TR]
[TD="align: right"]12541[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12542[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12543[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12544[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12545[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12546[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12547[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12548[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12549[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12550[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12551[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12552[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12553[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12555[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12561[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12563[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12564[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12565[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12566[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12567[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12568[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12569[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12570[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12571[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12572[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12574[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12575[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12577[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12578[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12580[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]12581[/TD]
[TD]New York[/TD]
[TD="align: right"]3905[/TD]
[/TR]
[TR]
[TD="align: right"]501[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]544[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10003[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10004[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10005[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10006[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10007[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10008[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10009[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10010[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10011[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10012[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10013[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10014[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10016[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10017[/TD]
[TD]New York[/TD]
[TD="align: right"]3901[/TD]
[/TR]
[TR]
[TD="align: right"]10018[/TD]
[TD]New York[/TD]
[TD="align: right"]3901
[/TD]
[/TR]
</tbody>[/TABLE]


What I need:
[TABLE="width: 473"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]STATE[/TD]
[TD]FROM_POSTAL_CODE[/TD]
[TD]TO_POSTAL_CODE[/TD]
[TD]ORG_CODE[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12540[/TD]
[TD="align: right"]12553[/TD]
[TD] 3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12555[/TD]
[TD="align: right"]12555[/TD]
[TD] 3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12561[/TD]
[TD="align: right"]12561[/TD]
[TD] 3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12563[/TD]
[TD="align: right"]12572[/TD]
[TD] 3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12574[/TD]
[TD="align: right"]12575[/TD]
[TD] 3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]12577[/TD]
[TD="align: right"]12578 [/TD]
[TD]3905[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]501 [/TD]
[TD]3901[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]544[/TD]
[TD] 3901[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]10001[/TD]
[TD="align: right"]10048[/TD]
[TD] 3901[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]10055[/TD]
[TD="align: right"]10055[/TD]
[TD] 3901[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]10060[/TD]
[TD="align: right"]10060[/TD]
[TD] 3901[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD="align: right"]10065[/TD]
[TD="align: right"]10065 [/TD]
[TD]3901[/TD]
[/TR]
</tbody>[/TABLE]

I've been spinning my wheels on this for over a week. This is my last ditch effort before pushing back and admitting I can't do this.

 

[MickG]

Give this a try for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG24Aug53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, oMin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRay() [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
oMin = Application.Min(Rng)
oMax = Application.Max(Rng)
ReDim ray(oMin - 1 To oMax + 1, 1 To 3)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    ray(Dn.Value, 1) = True
    ray(Dn.Value, 2) = Dn.Offset(, 2).Value
    ray(Dn.Value, 3) = Dn.Offset(, 1).Value
[COLOR="Navy"]Next[/COLOR] Dn
 
 c = 1
 ReDim nRay(1 To 4, 1 To 1)
nRay(1, 1) = "STATE": nRay(2, 1) = "FROM_POSTAL_CODE": nRay(3, 1) = "TO_POSTAL_CODE": nRay(4, 1) = "ORG_CODE"

[COLOR="Navy"]For[/COLOR] n = oMin To oMax
    [COLOR="Navy"]If[/COLOR] ray(n, 1) <> "" And ray(n - 1, 1) = "" [COLOR="Navy"]Then[/COLOR] Fst = n
    [COLOR="Navy"]If[/COLOR] ray(n, 1) <> "" And ray(n + 1, 1) = "" [COLOR="Navy"]Then[/COLOR] Lst = n
        [COLOR="Navy"]If[/COLOR] Fst <> 0 And Lst <> 0 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve nRay(1 To 4, 1 To c)
            nRay(1, c) = ray(Fst, 3)
            nRay(2, c) = Fst
            nRay(3, c) = Lst
            nRay(4, c) = ray(Fst, 2)
            Fst = 0: Lst = 0
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = Application.Transpose(nRay)
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Joe4]

Welcome to the Board!

Here is my stab at it ( have been messing around with it for a little while).
I sorted the data first. If you want this to happen on a different page (and keep the original data "as-is"), simply add code to copy and paste all the data to a new sheet.

Sub MyMacro()

    Dim lr As Long
    Dim r As Long

    Application.ScreenUpdating = False

'   Capture last row
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Sort by columns B, C, then A
    Range("A1:C" & lr).Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
'   Insert columns, move columns around, add titles, and format
    Columns("C:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").Copy
    Range("C1:D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("A:A").Delete Shift:=xlToLeft
    Range("B1") = "FROM_POSTAL_CODE"
    Range("C1") = "TO_POSTAL_CODE"
    Columns("B:C").EntireColumn.AutoFit

'   Loop through data
    r = 3
    Do Until Cells(r, "A") = ""
'       Check to see if columns A and D are the same as row above
        If (Cells(r, "A") = Cells(r - 1, "A")) And (Cells(r, "D") = Cells(r - 1, "D")) Then
'           Check to see if ending postal code is one more than row above
            If Cells(r, "C") - Cells(r - 1, "C") = 1 Then
'               Update ending value in row above and delete row
                Cells(r - 1, "C") = Cells(r, "C")
                Rows(r).Delete
            Else
'               Move down 1 row
                r = r + 1
            End If
        Else
'           Move down 1 row
            r = r + 1
        End If
    Loop

    Application.ScreenUpdating = True

End Sub

 

[jfm2143]

Thank you so much! I got your solution to work for me perfectly! You've done me a serious solid here, Joe4. Thank you for sharing your time and knowledge!

 

[jfm2143]

Hey MickG thank you so much! As a new member of this community, I'm so blown away to have received TWO solutions that worked perfectly! You and Joe4 have both done me a solid here, thank you for your time and know how.

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[MickG]

You're very welcome.