Is this possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rockofaith - 16 May 2006 18:11 GMT

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

Signature

rockofaith

Reply to this Message

Tom Ogilvy - 16 May 2006 18:31 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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,
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

rockofaith - 16 May 2006 18:54 GMT

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

Signature

rockofaith

Reply to this Message

Tom Ogilvy - 16 May 2006 19:14 GMT

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,Sheet2!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.

Signature

Regards,
Tom Ogilvy

> There has to be a more detailed reply than this isn't there?
>
[quoted text clipped - 10 lines]
>
> thanks again

Reply to this Message

rockofaith - 16 May 2006 19:42 GMT

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

Signature

rockofaith

Reply to this Message

Tom Ogilvy - 16 May 2006 20:06 GMT

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.

Signature

Regards,
Tom Ogilvy

> I have a sheet, that I'd need to put into an excel spreadsheet of
> course, but the sheet is layed out
[quoted text clipped - 32 lines]
> This will be awesome when I get it to work...it's taking fooorever to
> do this manually

Reply to this Message

rockofaith - 16 May 2006 21:44 GMT

Things are getting even more confusing...I'm not using vlookup, since I
read that could only be done if the column is in ascending order.

let me see if I can make this more clear...

I don't have the full zip codes on any files, we only have the first 3
everwhere. So that would eliminate the Left function...which is nice
to know you can do that, but I won't be needing that.

One .xls file will have 4 spread sheets "Recovered_Sheet1", "Ontario",
"Olive", and "Scranton".

The spreadsheet I want to match up to will just have 3 spreadsheets -
"Ontario", "Olive", and "Scranton".

Column I from the first .xls file on each of the city's spreadsheet has
the 3 digit zip code, which I want to look up from the respective
spreadsheet in the 2nd .xls file.

If there is a zip 010, I want it to look through the ranges, find where
it fits in the range and input in column H, what the zone # would be.

So I'm going to set up the 2nd .xls file with 3 columns, like so

A B C
000 003 N/A
004 005 8
010 349 8
350 358 7

Hopefully that explains exaclty what I want to do and hopefully you
misunderstood me before...because I don't understand your answer for
what I want to do.

Thanks so much

Signature

rockofaith

Reply to this Message

LTUser54 - 22 May 2006 15:47 GMT

rock-

sorry to hear you're not getting anywhere, even in this (very helpful)
excel forum.

You echo my concerns and complaints exactly.

Microsoft does not seem to care a fig about users like me (and you, and
probably millions of others) that are excluded from their geekdom
because we don't have $25 for a book probably written by a Microsoft
employee) to study WORTHWHILE examples, or take a 2 week course that
actually explains what parts of formulas do, and how to apply them to
the real world.

I have bitched about this for years.

Try typing in a help search using VLOOKUP and MATCH, and you get
NOTHING useful!!!

Best of luck with this. Unless you purchase a book, etc, it's unlikely
you will find out what "","" and other formula parts do. Microsoft
seems to really not give a f**k about explaining how to actually use
their formulas, and they get so caught up in explaining it with their
own nomenclature you need an interpreter to explain it in simple
english anyway!!!

Please remember that if you explain exactly what you are trying to do
to other members of this forum, you will probably get a better answer.

hang in there...

Mark
Boston

Signature

LTUser54

Reply to this Message