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.
Bookmarks