I have the following situation I could use some suggestions on. Worksheet 1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and create a
third worksheet that includes the latitude and longitude for the zip codes in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and create a
third worksheet that includes the latitude and longitude for the zip codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on. Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and create a
third worksheet that includes the latitude and longitude for the zip codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Yes, but I may need to explain my problem a little more. I want to be able
to dump a list of zip codes into the file and it spits out the lat and long
for those zip codes . My list could sometimes contain several hundred zip
codes. I know I can do this manually but I was hoping there was an easy way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and create a
third worksheet that includes the latitude and longitude for the zip codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on. Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and create a
third worksheet that includes the latitude and longitude for the zip codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Ok, then why not "dump" into Sheet2 (as it sounds like you are doing) and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to be
able
to dump a list of zip codes into the file and it spits out the lat and
long
for those zip codes . My list could sometimes contain several hundred zip
codes. I know I can do this manually but I was hoping there was an easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and create
a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Hold up on that..........

This is even better:

In Sheet2 B1 enter:

=SUMIF(Sheet1!$A$1:$A$42000,$A1,Sheet1!B$1:B$42000 )

Copy across to C1 then down as needed.

Biff

"Biff" wrote in message
...
Ok, then why not "dump" into Sheet2 (as it sounds like you are doing) and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to be
able
to dump a list of zip codes into the file and it spits out the lat and
long
for those zip codes . My list could sometimes contain several hundred
zip
codes. I know I can do this manually but I was hoping there was an easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Thanks for the help. That works great. Let me throw an other situation at
you. What if instead of 1 column that I wanted to compare, what if it were
two. for example, instead of the zip codes what if I had the City and State
in two separate columns and wanted to get the same results...the lat and long
for them. How could I alter the formula to have it compare two cells instead
of 1.
--
Mike


"Biff" wrote:

Hold up on that..........

This is even better:

In Sheet2 B1 enter:

=SUMIF(Sheet1!$A$1:$A$42000,$A1,Sheet1!B$1:B$42000 )

Copy across to C1 then down as needed.

Biff

"Biff" wrote in message
...
Ok, then why not "dump" into Sheet2 (as it sounds like you are doing) and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to be
able
to dump a list of zip codes into the file and it spits out the lat and
long
for those zip codes . My list could sometimes contain several hundred
zip
codes. I know I can do this manually but I was hoping there was an easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Suppose on Sheet1 column A is city, column B is state, column C is zip,
column D is lat, column E is long.........

Sheet2, column A is city, column B is state, column C is lat, column D is
long........

Formula for Sheet2 column C:

=SUMPRODUCT(--(Sheet1!$A$1:$A$42000=$A1)--(Sheet1!$B$1:$B$42000=$B1),Sheet1!C$1:C$42000)

Copy across to column D then down as needed.

Biff

"Otillio" wrote in message
...
Thanks for the help. That works great. Let me throw an other situation
at
you. What if instead of 1 column that I wanted to compare, what if it
were
two. for example, instead of the zip codes what if I had the City and
State
in two separate columns and wanted to get the same results...the lat and
long
for them. How could I alter the formula to have it compare two cells
instead
of 1.
--
Mike


"Biff" wrote:

Hold up on that..........

This is even better:

In Sheet2 B1 enter:

=SUMIF(Sheet1!$A$1:$A$42000,$A1,Sheet1!B$1:B$42000 )

Copy across to C1 then down as needed.

Biff

"Biff" wrote in message
...
Ok, then why not "dump" into Sheet2 (as it sounds like you are doing)
and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to
be
able
to dump a list of zip codes into the file and it spits out the lat and
long
for those zip codes . My list could sometimes contain several hundred
zip
codes. I know I can do this manually but I was hoping there was an
easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the
zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

I copied the formula into my document and set it up exaclty as you specified
but the results are not correct. It seems to be mulitplying and adding the
numbers together and the result is nowhere close to the information in Sheet
1 Columns D or E. Is there another way that you can think of or do you think
it is something on my side that is causing the problem.

Thanks for all you help on this. I really do appreciate the assistance.
--
Mike


"Biff" wrote:

Suppose on Sheet1 column A is city, column B is state, column C is zip,
column D is lat, column E is long.........

Sheet2, column A is city, column B is state, column C is lat, column D is
long........

Formula for Sheet2 column C:

=SUMPRODUCT(--(Sheet1!$A$1:$A$42000=$A1)--(Sheet1!$B$1:$B$42000=$B1),Sheet1!C$1:C$42000)

Copy across to column D then down as needed.

Biff

"Otillio" wrote in message
...
Thanks for the help. That works great. Let me throw an other situation
at
you. What if instead of 1 column that I wanted to compare, what if it
were
two. for example, instead of the zip codes what if I had the City and
State
in two separate columns and wanted to get the same results...the lat and
long
for them. How could I alter the formula to have it compare two cells
instead
of 1.
--
Mike


"Biff" wrote:

Hold up on that..........

This is even better:

In Sheet2 B1 enter:

=SUMIF(Sheet1!$A$1:$A$42000,$A1,Sheet1!B$1:B$42000 )

Copy across to C1 then down as needed.

Biff

"Biff" wrote in message
...
Ok, then why not "dump" into Sheet2 (as it sounds like you are doing)
and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to
be
able
to dump a list of zip codes into the file and it spits out the lat and
long
for those zip codes . My list could sometimes contain several hundred
zip
codes. I know I can do this manually but I was hoping there was an
easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the
zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike

Ooops!

Used the wrong column.

Should be:

=SUMPRODUCT(--(Sheet1!$A$1:$A$42000=$A1)--(Sheet1!$B$1:$B$42000=$B1),Sheet1!D$1:D$42000)

Then copied across to column D and then down as needed.

Biff

"Biff" wrote in message
...
Suppose on Sheet1 column A is city, column B is state, column C is zip,
column D is lat, column E is long.........

Sheet2, column A is city, column B is state, column C is lat, column D is
long........

Formula for Sheet2 column C:

=SUMPRODUCT(--(Sheet1!$A$1:$A$42000=$A1)--(Sheet1!$B$1:$B$42000=$B1),Sheet1!C$1:C$42000)

Copy across to column D then down as needed.

Biff

"Otillio" wrote in message
...
Thanks for the help. That works great. Let me throw an other situation
at
you. What if instead of 1 column that I wanted to compare, what if it
were
two. for example, instead of the zip codes what if I had the City and
State
in two separate columns and wanted to get the same results...the lat and
long
for them. How could I alter the formula to have it compare two cells
instead
of 1.
--
Mike


"Biff" wrote:

Hold up on that..........

This is even better:

In Sheet2 B1 enter:

=SUMIF(Sheet1!$A$1:$A$42000,$A1,Sheet1!B$1:B$42000 )

Copy across to C1 then down as needed.

Biff

"Biff" wrote in message
...
Ok, then why not "dump" into Sheet2 (as it sounds like you are doing)
and
use the formulas on that same sheet as well?

Assume the master table is on Sheet1 in the range A1:C42000

Suppose the zip codes are dumped into Sheet2 A1:An

In Sheet2 B1 enter one of these formulas:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A$1:$C$42000,COL UMNS($A:B),0))

=IF($A1="","",INDEX(Sheet1!$B$1:$C$42000,MATCH($A1 ,Sheet1!$A$1:$A$42000,0),COLUMNS($A:A)))

Copy across to C1 then down as needed.

Biff

"Otillio" wrote in message
...
Yes, but I may need to explain my problem a little more. I want to
be
able
to dump a list of zip codes into the file and it spits out the lat
and
long
for those zip codes . My list could sometimes contain several
hundred
zip
codes. I know I can do this manually but I was hoping there was an
easy
way
to automate this process.

--
Mike


"Biff" wrote:

Hi!

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the
zip
codes
in
worksheet 2.

Don't you already have that on Sheet1?

Biff

"Otillio" wrote in message
...
I have the following situation I could use some suggestions on.
Worksheet
1
is setup as follows and has 42,000 rows (all zips in the U.S.):

Zip Latitude Longitude

70504 59.443323 -150.343434
70507 45.343234 -34.488383
75075 85.343434 -35.959595

Worksheet 2 is a list of Zips Codes:

Zip
70504
70507

I would like to be able to compare worksheet 2 to worksheet 1 and
create a
third worksheet that includes the latitude and longitude for the
zip
codes
in
worksheet 2.

And suggestions???

Thanks,
--
Mike