URGENT - Merge/compare workbooks

[Guest]

I have two worksheets with a common unique identifier for each employee. One
worksheet has the date of employment, and the other worksheet has a list of
training courses that have been completed by each employee. I need to
identify employees hired within a date range and check to see if they have
all completed their courses. This would be fairly easy if I could merge the
spreadsheets and sort by date of employment. I've been told that the best way
to handle this is to do a comparative analysis using vlookup, but I can't
find any info on that process.

PLEASE HELP!

 

[Guest]

Hi Trish,
Assume that, on Sheet1, the emp ID is in column A, the employment date is in
column B, and column C is empty.
Assume that, on Sheet2, the emp ID is in column A and the training dates are
in column B.
Enter this formula in cell C1 on Sheet1. You'll need to adjust the $B$5
reference to reflect the last row of data on Sheet2. Then copy the formula
down.

=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)

HTH

 

[Guest]

Thanks Ken! That's got me on the right track, but it only merged the header
in row 1, not the data in rows 2 through 5. Any thoughts?

 

[Guest]

Please post back a sample of each worksheet so I can see the layouts.

 

[Guest]

Ken-
It's basically the same as your assumptio:

Sheet 1
Column A Column B Column C
UNID DOE (blank)
101 1/1/2004
102 3/4/2000
103 6/7/2004
104 8/9/2002

Sheet 2
Column A Column B Column C
UNID Training (blank)
101 Y
102 N
103 Y
104 Y

I have Sheet 1 & sheet 2 in the same workbook.

Thanks!
-Trish

 

[Guest]

Okay, so put this formula in cell C2 on Sheet1.

=VLOOKUP(A2,Sheet2!$A$2:$B$5,2,FALSE)

Change the number 5 in the $B$5 part of the entry to the last row number on
sheet2.

It will use the ID from cell A2 on sheet1, go to Sheet2, find the ID in
column A, and return the corresponding entry from column B on Sheet2.

The you'll need to copy the formula in cell C2 down the rest of the rows in
column C.

 

[Guest]

Ken-
That took care of the data for row 2, but it's still only populating one row
at a time. I can drag the formula down from C1 to C5, but it may become a
bit cumbersome later as I anticipate adding 3,000 to 4,000 name this year.

Any other help you can give on populating more than one row would be
appreciated, but in the meantime... thank you for giving me a very good start!
-Trish

 

[Guest]

Trish,
Here is an Excel tip that should help you.
Put the formula into cell C2.
Click on C2 to select the cell.
Put the cursor on the lower right corner of the cell and it will turn into a
cross.
Double click on the cross and the formula will copy down automatically to
the last row with an entry in column B.

 

[Guest]

AWESOME! Thanks Ken! That solves it all now.
I really appreciate all of your help on this!