Filter ZIP codes into ranges

[ronsmail]

I am working on a project to take a list of ZIP codes (29,133 rows) an
sort it by territory, then find all ZIPs in that territory that are i
sequential order and make a ZIP range from the 2 extremes in th
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 group
of ZIP codes sequentially ordered and append the first ZIP in the grou
to column X and the last ZIP in the group to Column Y

 

[Mike S]

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

 

[mscir]

I can write the code to do the sorting and sequential check yo
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? O
do you have it in a spreadsheet such that you cold save it to a csv fil
and send it to me? If so I will write up a quick program and send yo
the program and the source code so you can see how I did it.

Mik

 

[ronsmail]

I can write the code to do the sorting and sequential check yo
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? O
do you have it in a spreadsheet such that you cold save it to a csv fil
and send it to me? If so I will write up a quick program and send yo
the program and the source code so you can see how I did it.

Mike

It is confidential client data. But I can strip out the confidentia
data and just send you the list of zip codes. I would have done thi
myself but I was only given 48 hours to do it. Spending much of tha
time in airports doesn't help. I appreciate the help.

Ro

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Vacuum Sealed]

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.

 

[mscir]

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

 

[Vacuum Sealed]

That looks a lot more straight-forward, not to mention easier, than what
I proposed.

Mike

--- Posted via news://freenews.netfront.net/ - Complaints to
(e-mail address removed) ---

Happy to help.