Comparing Spreadsheets to find information

  • Thread starter Thread starter Jini
  • Start date Start date
J

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!
 
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
 
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
 
Back
Top