Comparing Spreadsheets to find information

[Jini]

I have two spreadsheets. The master one has cost centers,
city and states. The second one has staff names and cost
centers. I need to pull in from the master the city and
state for each staff member (i'm talking about 4,000
people). Is there a way I can have a macro or something
go to the master worksheet and search for the cost center
and if found pull in the city and state for that person?
If I have a macro I don't mind doing it line by line (well
I do, but can't think of a faster method).

HELP PLEASE!

 

[DSC]

Hi Jini

You Could use the VLOOKUP Function for this

Something Like

=VLOOKUP($A1,Master!$A$1:$C$10,2,FALSE)

Where $A1 is the Value you Want to Match
Master!$A$1:$C$10 is the Table where your data is held
2 to tell which Column to Show
And FALSE to Search for Text Exactly

Just Change the Values to what you need

HTH
Davi

 

[Guest]

The easy solution is to use SQL. The first problem is that you have 2 data sources (workbooks).

1. Use Excel as an automation server, open each workbook and save the relevant sheet in each in CSV format (if it is a one off, you can do this manually)
2. Use the ODBC text driver to read each CSV and to join them
3. Write the resulting recordset to a CSV or write it directly to a workbook. (lookup CopyFromREcordset in the Excel VBA help file)

On 2: (this is the (untested) code you want

Cnn ="Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\;
Sql = "SELECT * FROM FIRST.CSV a,SECOND.CSV b WHERE a.[Cost centre]=b.[Cost Centre]
Set RS = CreateObject("ADODB.Recordset"
RS.Open Sql, Cn