[RESOLVED] Returning zip code ranges for a rep

[scoobster]

Hi

I have an Excel workbook with 2 columns (Zip Code and Rep). I want to show the data so that for each rep I can see the range of zip codes. For example.

This is a sample of data;

Zip Rep
1001 4108
1002 4108
1003 4108
1004 4108
1005 4108
1007 4108
1008 4108
1009 4108
1010 4108
1011 4108
1012 4108
1013 4111
1014 4111
1020 4111
1021 4111
1022 4108
1026 4108
1027 4108
1028 4111

And what I want to get is the following;

ZipFrom ZipTo Rep
1001 1012 4108
1013 1021 43111
1022 1027 4108
1028 1028 4111

So I can easily see that rep 4108 is covering all zip codes from 1001 to 1012, and that 43111 is covering from 1013 to 1021, etc.

Is it possible?

I tried pivot table using max and min on the zip code but that didn't give me the ranges I needed. Can a formula solve this or does it need VBA.

Any help is greatly appreciated.

Thanks

[vbfbryce]

I have the zip code data in column A, and the rep in column B (with headers in row 1). Assuming the data is sorted first by zip, this code will write the "summary" info to columns D, E and F (not sure what you wanted to do with the summary...):

Sub repZip()
    Dim i As Long
    Dim lastRow As Long
    Dim currRep As String
    Dim startRep As Long    'row where rep starts
    Dim endRep As Long      'row where rep ends
    Dim writeRow As Long    'row to write the summary
        
    lastRow = Range("a" & Rows.Count).End(xlUp).Row 'bottom of data
    currRep = Range("b2").Value
    startRep = 2
    
    For i = 3 To lastRow
        If Range("b" & i).Value <> currRep Then
            'found next rep
            endRep = i - 1
            writeRow = Range("d" & Rows.Count).End(xlUp).Row + 1
            Range("d" & writeRow).Value = Range("a" & startRep).Value
            Range("e" & writeRow).Value = Range("a" & endRep).Value
            Range("f" & writeRow).Value = currRep
            If i = lastRow Then
                writeRow = Range("d" & Rows.Count).End(xlUp).Row + 1
                Range("d" & writeRow).Value = Range("a" & i).Value
                Range("e" & writeRow).Value = Range("a" & i).Value
                Range("f" & writeRow).Value = Range("b" & i).Value
                Exit Sub
            End If
            startRep = i
            currRep = Range("b" & i).Value
        Else
            If i = lastRow Then 'end of data, rep is same as in row above
                endRep = i
                Range("d" & Rows.Count).End(xlUp).Value = Range("a" & startRep).Value
                Range("e" & Rows.Count).End(xlUp).Value = Range("a" & endRep).Value
                Range("f" & Rows.Count).End(xlUp).Value = currRep
            End If
        End If
    Next i
End Sub

[scoobster]

many thanks. That is almost meeting my needs 100% except I noticed that I could have a single zip code for a user and the program ignore that in the results. For example;

1007 223108
1008 223108
1009 223108
1010 Peter
1011 223108
1012 223108

This gives me;
1007 1009 223108
1011 1012 223108

But I need to see;
1007 1009 223108
1010 1010 Peter
1011 1012 223108

Thanks again for your quick reply and support.

[vbfbryce]

Sorry about that...didn't handle the "last row when same as previous row condition," so it was writing over the previous summary record.

Change to the following:

Sub repZip()
    Dim i As Long
    Dim lastRow As Long
    Dim currRep As String
    Dim startRep As Long    'row where rep starts
    Dim endRep As Long      'row where rep ends
    Dim writeRow As Long    'row to write the summary
        
    lastRow = Range("a" & Rows.Count).End(xlUp).Row 'bottom of data
    currRep = Range("b2").Value
    startRep = 2
    
    For i = 3 To lastRow
        If Range("b" & i).Value <> currRep Then
            'found next rep
            endRep = i - 1
            writeRow = Range("d" & Rows.Count).End(xlUp).Row + 1
            Range("d" & writeRow).Value = Range("a" & startRep).Value
            Range("e" & writeRow).Value = Range("a" & endRep).Value
            Range("f" & writeRow).Value = currRep
            If i = lastRow Then
                writeRow = Range("d" & Rows.Count).End(xlUp).Row + 1
                Range("d" & writeRow).Value = Range("a" & i).Value
                Range("e" & writeRow).Value = Range("a" & i).Value
                Range("f" & writeRow).Value = Range("b" & i).Value
                Exit Sub
            End If
            startRep = i
            currRep = Range("b" & i).Value
        Else
            If i = lastRow Then 'end of data, rep is same as in row above
                endRep = i
                writeRow = writeRow + 1     'changed this ******
                Range("d" & writeRow).Value = Range("a" & startRep).Value   'and this
                Range("e" & writeRow).Value = Range("a" & endRep).Value     'and this
                Range("f" & writeRow).Value = currRep   'and this *******************
            End If
        End If
    Next i
End Sub

[scoobster]

Superb. Does exactly what I what it to do and very quickly too.

Many thanks.