Is this possible?

[rockofaith]

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

[Tom Ogilvy]

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
>
>

[rockofaith]

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

[Tom Ogilvy]

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.

--
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
>
>

[rockofaith]

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

[Tom Ogilvy]

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
>
>

[rockofaith]

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

[LTUser54]

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