Comparing files and extracting info.

O

Otillio

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

Biff

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.
Click to expand...

Don't you already have that on Sheet1?

Biff
 
O

Otillio

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

Biff

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,COLUMNS($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
 
B

Biff

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
 
O

Otillio

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

Biff

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
 
O

Otillio

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

Biff

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
 
Top