Linking zipcodes to a regional base

Hi there fellow Excel'ers!

I have a question. You see, I'm trying to find out a way to link data on zip codes with regional operating firms.

First off, these firms operate throughout a country covering their own distinct regions. I can get specific datasets on the zip code level, but need these data (numbers, not text) aggregated to the regional level (kind of like the county-level). I can find out what zip codes are in each region, and thus could - manually - use a lot of time taking the data from maybe 20 zip codes in a region, and add them up to get that specficic region's data, and do the same procedure for the rest of the regions and zip codes. However, I often get updated zip code data, so it would be very nice to have Excel or a macro or something that can merely refer the data cell next to the specific zip code with the specific region (after I have - naturally only once - noted in the formula or what ever, which zip code belongs to which region...)

It probably sounds a little complecated, so here is an example:

I might get these data:
zip code | data:
1234 | 35340
7654 | 56000
5231 | 23000
4116 | 45300
6723 | 60000

What I need:
I can find out which zip codes belong to which regions, but linking the data each month would take lots of time. So, optimally, I would somehow (only once) specify that a range of zip codes "belong" to a specific region, another range belongs to another region etc. until I cover the whole country. And then, when I get the data the next time, I can simply refer those data to a formula or a macro, and it automatically finds the zip codes of each region, and sums up their data. For instance:

If I know that zip codes 1234 and 7654 belongs to, say, "Michigan", the data 35340 and 56000 are added to get 91340 for Michigan (and so on for the rest of the zip codes/regions).

So you see, I want to remove the hassle of each month pairing up zip codes (which are random within these regions) with regions to aggregate the zip code data in order to reach a regional result. Via some Excel formula or a macro, it should definitely be possible for me to only write down maybe two columns of zip code/region texts like:

zip code | region:
1234 | Michigan
7654 | Michigan
5231 | Detroit
6723 | Detroit
4116 | Detroit

And use this over and over again, to pair data from the zip code with the region in question - in stead of writing the data in hand everytime I get updated data.


I hope someone can help and that you understood my very long question :-)



Best regards,

Niko

Well, the zip codes aren't US, I just mentioned Michigan and Detroit as examples - however, what zip codes we're talking about shouldn't matter. It's still just a matter of linking a bunch of distinct numbers with some specific regions - and only doing that once - instead of every time new data arrives.

I know: zip code | region | data - and only want to write up the zip code along with the region once - as I get new data on zipcodes often - and need to refer that to specific regions.

Hello ncikusa,

I think it would help everyone to understand this problem better if you could post a workbook with a representative amount of data for review.

Sincerely,
Leith Ross