Formula to separate rows in a customer list based on Zip Code.

A little background:

We have a small family business and are doing our best to transition from paper to electronic. We are working with a company that is helping us get our customer database in order so they can put it into their system which would allow us to use their program and make our lives a billion times easier. They are requiring us to do certain things to the database to continue any further. I guess that's the terms they worked out with the bosses. I don't know lol.

Anyway, first on the list is separating all of our customers into zones based on Zip codes. We don't have a "tech guy" so I can't have him do it and I am 1 of 2ish guys that kinda did computer stuff in high school, ya know what I mean?

The Problem:

So picture a database with over 2,000 customers in rows. Then columns with things like Name, address, city, state, zip. When discussing this with the other sorta computer guy, I thought there was a way to pull entire rows based on a number and keeping them in alphabetical order. The number being the Zip code in this case.

I pulled up a map of Zip codes for my city. To make it simple lets just say our "Zones" will be North, South, East, West. All 2k+ customers will need to have an additional column added stating what zone they are in. Let's say I want to start with the Southern Zone. So I look at the map and it has zip codes 11111, 11112, 11113, 11114, 11115. Is there a formula that would search the database for those zip codes, pull all rows with those zip codes and separate them out? Here is my other problem, the finished database would then need to be put back in order alphabetically with that new Zone column.

The company we are working with wants other things done as well but this our main headache at the moment. We just don't have the budget for a tech guy :/

I could be completely wrong about a formula or function or even the method I am thinking about how to go about it. Any help would be much appreciated.

Thank you so much for your time!

Hello AIceStL,

I'm not totally sure what you have in mind but here's something that may head you off in the right direction.

The data base you have could be split up into zones as you have mentioned with each zone having its own worksheet. In your "Master" data base, a "Zone" column could be added to indicate which post code should be allocated to that zone. Hence, once a customer's details are entered in a row, you select a Zone from a drop down in the last cell of the row (the Zone column).
Once all your data has been entered, a VBA code assigned to a button could separate each customer into their relevant zone and then that data transferred to the relevant zone worksheet. That code could be as follows:-

Please Login or Register  to view this content.

I've attached a mock-up work book just to show you how it could work. Click on the "RUN" button to see it work.
The Zone column has a drop down in each cell created in Data Validation. You select the relevant Zone once you decide which Zone a post code ( Column F) belongs to.

Its a starting point for you and perhaps someone else on this forum may have other ideas to offer up.

I hope that this helps.

Cheerio,
vcoolio.

Ok got it done. Thanks!

Originally Posted by vcoolio

Hello AIceStL,

I'm not totally sure what you have in mind but here's something that may head you off in the right direction.

The data base you have could be split up into zones as you have mentioned with each zone having its own worksheet. In your "Master" data base, a "Zone" column could be added to indicate which post code should be allocated to that zone. Hence, once a customer's details are entered in a row, you select a Zone from a drop down in the last cell of the row (the Zone column).
Once all your data has been entered, a VBA code assigned to a button could separate each customer into their relevant zone and then that data transferred to the relevant zone worksheet. That code could be as follows:-

Please Login or Register  to view this content.

I've attached a mock-up work book just to show you how it could work. Click on the "RUN" button to see it work.
The Zone column has a drop down in each cell created in Data Validation. You select the relevant Zone once you decide which Zone a post code ( Column F) belongs to.

Its a starting point for you and perhaps someone else on this forum may have other ideas to offer up.

I hope that this helps.

Cheerio,
vcoolio.

That is very helpful! Thank you very much. Is there any way to combine the separate list again now that I have the zones? Can it be alphabetized by customer name after that? I will continue to research online. Thank you again you have been very helpful.

Hello AIceStL,

Is there any way to combine the separate list again now that I have the zones? Can it be alphabetized by customer name after that?

If you are referring to the Master sheet as per the mock-up sample and placing all the data back into the Master sheet, then I would say that there is no need to do this.
All data can remain as is in the Master sheet, you can then sort A-Z based on the customer ID rather than name. Thus, each time that you need to, you can separate the zones, already sorted A-Z, into their respective sheets. You'll then have a sorted master data base and separate zone sheets for quick analysis.
The code can be adjusted to do all this for you on the click of a button.

I hope that this helps.

Cheerio,
vcoolio.