populate city and sate in excel 2007 or access or SQL

[jharl]

Hello all, and thanks for looking and maybe answering my question. I am it administrator and I know excel moderately. I have an excel spreadsheet with two workbooks in the first workbook I have table1 that has data as follows: company,street address phone and zip code. the city and state fields are there but empty. In the other sheet I have a complete zip code table,table2 with the data as follows: City, state, zip.

How can I populate city and state based on the zip code feiled I have in table 1 with data from city and state from table2.

I also can do this in sql or access as I have these on my PC. I just do not have the know how. if you are going to help please provide the a sql statement or function

Thanks again
John

 

[SkipVought]

John,

You're not very clear about the TERMS you are using.

in the first workbook I have table1

In the other sheet I have a complete zip code table,table2

a WORKBOOK is an Excel file, conprised of one or more WORKSHEETS.

So is it two WORKBOOKS, or One WORKBOOK with two WORKSHEETS?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jharl]

Sorry, I have one workbook with two worksheets. I home that is more clear.

john

 

[SkipVought]

You can query the two sheets as tables and join them, in the Data tab. I'm doing this from memory, as all I have at work is 2003.

On the Data ribbon is a Get External Data, or something like that. Select EXCEL Driver and drill down to the workbook. As you follow the 'bouncing ball' you will be able to add the sheets into the query grid. From there, it's pretty much like MS Access.

Finally File > Return data to Microsoft Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]