Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Want help to filter zip codes to ranges in Microsoft Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 24-02-2012
Member
 
Join Date: Feb 2012
Posts: 10
Want help to filter zip codes to ranges in Microsoft Excel

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.

Reply With Quote
  #2  
Old 24-02-2012
Member
 
Join Date: Jul 2011
Posts: 432
Re: Want help to filter zip codes to ranges in Microsoft Excel

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.
Reply With Quote
  #3  
Old 24-02-2012
Member
 
Join Date: Jul 2011
Posts: 432
Re: Want help to filter zip codes to ranges in Microsoft Excel

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

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Want help to filter zip codes to ranges in Microsoft Excel"
Thread Thread Starter Forum Replies Last Post
Automatic Filter on Hyperlink in Excel Mike Chester Software Development 2 17-03-2012 01:36 PM
Want help to filter merge cell in Microsoft Excel DEvOTeEL MS Office Support 2 24-02-2012 07:00 PM
Want to convert postal codes to numeric values in Microsoft Excel New!life MS Office Support 2 23-02-2012 07:13 PM
How to sort report filter values of Pivot Table in Microsoft Excel Lakshaki MS Office Support 2 24-01-2012 02:07 PM
How to Auto Filter in Excel Lanka Boy Windows Software 2 07-01-2012 09:17 PM


All times are GMT +5.5. The time now is 02:05 PM.