I have a list of zip codes that I need attach zone numbers to. I wish
it were as easy as saying all zip codes from 00001-20000 = zone 1,
20001-40000 = zone 2...but they're all over the place.

So, I'd need to do something like zone = 1 if (000-003) or (588-595) or
770-778) or etc.

So I have one column that gives the zone number and another column that
gives the first three digits of the zip code.

Any ideas?

Thanks


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599

Vlookup supports looking up data in a table and returning an associated value
if the data to be looked up is in the leftmost column. If not use a
combination of Index and Match. See help for details.

These may be used in code as well.

--
Regards,
Tom Ogilvy


"rockofaith" wrote:


I have a list of zip codes that I need attach zone numbers to. I wish
it were as easy as saying all zip codes from 00001-20000 = zone 1,
20001-40000 = zone 2...but they're all over the place.

So, I'd need to do something like zone = 1 if (000-003) or (588-595) or
770-778) or etc.

So I have one column that gives the zone number and another column that
gives the first three digits of the zip code.

Any ideas?

Thanks


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599

There has to be a more detailed reply than this isn't there?

I've been looking through the help for the past 45 minutes, with no
luck. If I use vlookup apparently I have to have the column assorted
in ascending order and I'm not able to do that with the way everything
else is set up.

If I have a spreadsheet that has columns with ranges and zones could I
match it up to this other spread sheet? Like if I had in Column A1
(000-003) A2 (004-005), and then in Column B1 (1), B2 (2) etc...could I
match it so that in the other spreadsheet it would see that a zip code
in the range of A1 would enter a 1 in the zone column?

thanks again


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599

You said:

So I have one column that gives the zone number and another column that
gives the first three digits of the zip code.

that means you have
1 000
1 001
1 002
1 003
2 999
1 432
3 333


assume this is on sheet2 in columns A and b

If on sheet1 in A1 I put

43226

in B1
=if(A1="","",Index(Sheet2!A:A,Match(Left(A1,3)*1,S heet2!B:B,0),1))

then it should return a 1. If the 3 digit numbers are stored as Text, then
remove the *1. Note that the data is not sorted.

If you have some list with

1 000-003
2 432-500
1 222-333

then I doubt that would be very useful with a worksheet function. (or even
if the columns were reversed)

If you had

000 003 1
004 005 2

with 3 colunms, then you could use the Vlookup with the fourth argument to
reflect sorted data. (essentially column B is not used).



You could certainly loop through it, break out the two numbers and do a
comparison with the first 3 digits of your zip code to see when you fine a
range that includes this 3 digit number.

You could tie such a macro to the change event.

--
Regards,
Tom Ogilvy


"rockofaith" wrote:


There has to be a more detailed reply than this isn't there?

I've been looking through the help for the past 45 minutes, with no
luck. If I use vlookup apparently I have to have the column assorted
in ascending order and I'm not able to do that with the way everything
else is set up.

If I have a spreadsheet that has columns with ranges and zones could I
match it up to this other spread sheet? Like if I had in Column A1
(000-003) A2 (004-005), and then in Column B1 (1), B2 (2) etc...could I
match it so that in the other spreadsheet it would see that a zip code
in the range of A1 would enter a 1 in the zone column?

thanks again


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599

I have a sheet, that I'd need to put into an excel spreadsheet of
course, but the sheet is layed out

000-003 5
004-005 6

So, I should have them in separate columns I understand from your
reply.

I'm not quite understanding the functions in your formula.

What I want to do is have a master workbook...

Each workbook I have has 4 sheets...sheet 1 has all the info, sheets
2-4 break up the information on sheet 1 in 3 different categories.

So I want to have a master work book that has 3 sheets, one that has
the zip and zone codes to match with the sheet 2 in the first workbook,
the second one to match with the 3rd sheet...and sheet 3 to match with
the 4th sheet.

Your formula

=if(A1="","",Index(Sheet2!A:A,Match(Left(A1,3)*1,S heet2!B:B,0),1))

1 - what does the "","" do? I would assume it would take the range if
I had the numbers "000-003" in one column.

2 - What does the Left(A1,3) do?

3 - What does the B:B,0 do?

Thanks so much for your help.

This will be awesome when I get it to work...it's taking fooorever to
do this manually


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599

Not much that can be done with the table you show. You can insert a blank
column B, then do data=Text to columns and break it into 3 columns, then use
a lookup function/match. I have demo't this as has Executor in the other
thread

=if(A1="","",something else)
says, if A1 is blank display a blank. If you just did

=vlookup(A1,range,2,false)

then if A1 is empty, it would return #N/A or an incorrect value depending on
the formula.

Left(A1,3) takes the left 3 digits of a 5 digit zip code.

Sheet2!B:B designates to look up the 3 digits in column B of sheet2.

--
Regards,
Tom Ogilvy




"rockofaith" wrote:


I have a sheet, that I'd need to put into an excel spreadsheet of
course, but the sheet is layed out

000-003 5
004-005 6

So, I should have them in separate columns I understand from your
reply.

I'm not quite understanding the functions in your formula.

What I want to do is have a master workbook...

Each workbook I have has 4 sheets...sheet 1 has all the info, sheets
2-4 break up the information on sheet 1 in 3 different categories.

So I want to have a master work book that has 3 sheets, one that has
the zip and zone codes to match with the sheet 2 in the first workbook,
the second one to match with the 3rd sheet...and sheet 3 to match with
the 4th sheet.

Your formula

=if(A1="","",Index(Sheet2!A:A,Match(Left(A1,3)*1,S heet2!B:B,0),1))

1 - what does the "","" do? I would assume it would take the range if
I had the numbers "000-003" in one column.

2 - What does the Left(A1,3) do?

3 - What does the B:B,0 do?

Thanks so much for your help.

This will be awesome when I get it to work...it's taking fooorever to
do this manually


--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542599