Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Filter ZIP codes into ranges
I am working on a project to take a list of ZIP codes (29,133 rows) and sort it by territory, then find all ZIPs in that territory that are in sequential order and make a ZIP range from the 2 extremes in the sequential group.
The logic behind what I want to do goes like this: Sort by Region code, then by Zone #, then by ZIP code. Find all groups of ZIP codes sequentially ordered and append the first ZIP in the group to column X and the last ZIP in the group to Column Y. Regions are made up of territories, territories are mad up of zones. The list should be sorted by zip code in ascending order. All I need it to do is to take all of the zip codes that are in sequential order an put the first one in the sequential group in a cell to the right and the last one in the sequence in the cell to the right of that. Or the entire sequence can be made in to a range of the extremes in the group. For example: Zip Code Zone Region Code Territory Result 07002 0 R6 R6-0 07002 07003 5 R6 R6-5 07003-07004 07004 5 R6 R6-5 07006 0 R6 R6-0 07006-07007 07007 0 R6 R6-0 07009 0 R6 R6-0 07009 07017 0 R6 R6-0 07017-07019 07018 0 R6 R6-0 07019 0 R6 R6-0 The ranges are broken out by zone. I am putting these in a database to track sales. Unless I condense the consecutive zips into ranges I will have 29,000 rules in the database, which will choke my computer. My goal is to cut that down to 14,000 - 15,000 zip code rules. I hope that helps. Last edited by ronsmail : January 28th 12 at 02:27 AM Reason: better explanation. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter ZIP codes into ranges
On 1/26/2012 9:53 AM, ronsmail wrote:
I am working on a project to take a list of ZIP codes (29,133 rows) and sort it by territory, then find all ZIPs in that territory that are in sequential order and make a ZIP range from the 2 extremes in the sequential group. The logic behind what I want to do goes like this: Sort by Region code, then by Zone #, then by ZIP code. Find all groups of ZIP codes sequentially ordered and append the first ZIP in the group to column X and the last ZIP in the group to Column Y. I would consider doing everything with arrays to make troubleshooting easy, and to keep the code fast: Store the data for region, zone #, and zip in a text file or other database file that can be read from Excel. Read the text file into an array: read the whole file into a string the split it based on the vbcrlf at the end of each line. Sort the array by region. Copy the array elements that have the data you're interested in, minus the no longer needed region field, to a 2nd array. Erase the first array to save memory. Sort the 2nd array by zone #. Copy the array elements that have the data you're interested in, minus the no longer needed zone #, to a 3rd array. Erase the second array to save memory. Sort the 3rd array by zip. Now it will be easy to check for sequential values and display them in Excel. Mike |
#3
|
|||
|
|||
I can write the code to do the sorting and sequential check you described in VB6, you can easily adapt it to Excel. Is the data private? If not, do you have a flat text file that has all of the data in it? Or do you have it in a spreadsheet such that you cold save it to a csv file and send it to me? If so I will write up a quick program and send you the program and the source code so you can see how I did it.
Mike |
#4
|
|||
|
|||
Quote:
Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter ZIP codes into ranges
On 27/01/2012 4:53 AM, ronsmail wrote:
I am working on a project to take a list of ZIP codes (29,133 rows) and sort it by territory, then find all ZIPs in that territory that are in sequential order and make a ZIP range from the 2 extremes in the sequential group. The logic behind what I want to do goes like this: Sort by Region code, then by Zone #, then by ZIP code. Find all groups of ZIP codes sequentially ordered and append the first ZIP in the group to column X and the last ZIP in the group to Column Y. Hi Ron Try this link for Debra Dalgliesh http://www.contextures.com/xlDataVal15.html It provides the steps for setting up a dependent data validation list which may help with what you are looking for. HTH Mick. |
#6
|
|||
|
|||
Quote:
Here is a CSV of generic data. Can you explain how to adapt the VB6 to excel? Last edited by ronsmail : January 27th 12 at 08:45 PM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter ZIP codes into ranges
On 1/27/2012 5:05 AM, Vacuum Sealed wrote:
On 27/01/2012 4:53 AM, ronsmail wrote: I am working on a project to take a list of ZIP codes (29,133 rows) and sort it by territory, then find all ZIPs in that territory that are in sequential order and make a ZIP range from the 2 extremes in the sequential group. The logic behind what I want to do goes like this: Sort by Region code, then by Zone #, then by ZIP code. Find all groups of ZIP codes sequentially ordered and append the first ZIP in the group to column X and the last ZIP in the group to Column Y. Hi Ron Try this link for Debra Dalgliesh http://www.contextures.com/xlDataVal15.html It provides the steps for setting up a dependent data validation list which may help with what you are looking for. HTH Mick. That looks a lot more straight-forward, not to mention easier, than what I proposed. Mike --- Posted via news://freenews.netfront.net/ - Complaints to --- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter ZIP codes into ranges
On 28/01/2012 12:27 PM, mscir wrote:
On 1/27/2012 5:05 AM, Vacuum Sealed wrote: On 27/01/2012 4:53 AM, ronsmail wrote: I am working on a project to take a list of ZIP codes (29,133 rows) and sort it by territory, then find all ZIPs in that territory that are in sequential order and make a ZIP range from the 2 extremes in the sequential group. The logic behind what I want to do goes like this: Sort by Region code, then by Zone #, then by ZIP code. Find all groups of ZIP codes sequentially ordered and append the first ZIP in the group to column X and the last ZIP in the group to Column Y. Hi Ron Try this link for Debra Dalgliesh http://www.contextures.com/xlDataVal15.html It provides the steps for setting up a dependent data validation list which may help with what you are looking for. HTH Mick. That looks a lot more straight-forward, not to mention easier, than what I proposed. Mike --- Posted via news://freenews.netfront.net/ - Complaints to --- Happy to help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with zip codes and filter | Excel Discussion (Misc queries) | |||
Need to convert list of 5 digit zip codes to ranges where possible | Excel Worksheet Functions | |||
Date ranges and advanced filter | Excel Programming | |||
How to create ranges with auto filter | Excel Programming | |||
Multiple ranges to Auto Filter | Excel Programming |