Help! how to cross - data with excel??
 

Hi, I'm required to present some info at my work, using two excel files. This
is the task:

File 1, has ID numbers in column A, and column B is blank.

File 2, has ID numbers in column C and column D has the date the person
joined the company

File 1 es a select grop of people (about 2500), whereas File 2 is the
general database (about 30thou)

They're asking me to match the data, in order to end up with the
coresponding date they joined the company in column B on File 1 (obtained
from column D on file 2), how to do it? taking in account that File 1 is
outdated and some people might not be in file 2.

I would really appreciate any help you might give me.


Gloria

Help! how to cross - data with excel??
 

You have a response at one of your other posts.

gloriamar wrote:

Hi, I'm required to present some info at my work, using two excel files. This
is the task:

File 1, has ID numbers in column A, and column B is blank.

File 2, has ID numbers in column C and column D has the date the person
joined the company

File 1 es a select grop of people (about 2500), whereas File 2 is the
general database (about 30thou)

They're asking me to match the data, in order to end up with the
coresponding date they joined the company in column B on File 1 (obtained
from column D on file 2), how to do it? taking in account that File 1 is
outdated and some people might not be in file 2.

I would really appreciate any help you might give me.

Gloria

--

Dave Peterson

Help! how to cross - data with excel??
 

1) Open both the files File 1 & File 2.
2) Place the cursor in B2 cell of File1 (Since the first row will have the
header - ID Number)

Press = for enabling to formula mode and type VLOOKUP(A2,
Press Alt Tab which will get you to the File2 or Click the File2 in the Task bar and select C:D column and give space and type 2,False

Or

Simply copy and paste the below formula in B2 cell of File1 and change the
FILE2 from the below formula to your Second File name
=VLOOKUP(A1,[FILE2.xls]Sheet1!$C:$D,2,FALSE)

For Displaying the formula results in Dates:-
Now select the File1 - B Column and Do right click Select Format
CellsNumberCategoryDateTypeSelect your desired Date Format and give
Ok.

For Converting the formula result to Values:-
Select and copy the Entire B Column and Do right clickPaste
SpecialSelect Values Option Button and give Ok€¦

Have a look in the below links for more details about Vlookup:-
http://office.microsoft.com/en-us/ex...093351033.aspx
http://www.contextures.com/xlfunctions02.html
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm
http://www.techonthenet.com/excel/formulas/vlookup.php

Hope its clear.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"gloriamar" wrote:

Hi, I'm required to present some info at my work, using two excel files. This
is the task:

File 1, has ID numbers in column A, and column B is blank.

File 2, has ID numbers in column C and column D has the date the person
joined the company

File 1 es a select grop of people (about 2500), whereas File 2 is the
general database (about 30thou)

They're asking me to match the data, in order to end up with the
coresponding date they joined the company in column B on File 1 (obtained
from column D on file 2), how to do it? taking in account that File 1 is
outdated and some people might not be in file 2.

I would really appreciate any help you might give me.


Gloria

Help! how to cross - data with excel??
 

Try this formula in column B of File 1:

=IF(ISNA(MATCH(A1,INDIRECT("'[File 2.xls]Sheet name'!C1:C30000"),0)),"ID not
found in File 2",VLOOKUP(A1,INDIRECT("'[File 2.xls]Sheet
name!C1:D30000"),2,FALSE))

Drag-fill the formula down column B.

Note, this assumes the first ID number in File 1 is in cell A1, and the ID
numbers in File 2 are in C1:D30000. You will need to change these ranges in
the formula as fits the actual location of your data. You will also need to
change "Sheet name" to the actual worksheet name in File 2 that holds the ID
data.

Regards,

Tom


"gloriamar" wrote:

Hi, I'm required to present some info at my work, using two excel files. This
is the task:

File 1, has ID numbers in column A, and column B is blank.

File 2, has ID numbers in column C and column D has the date the person
joined the company

File 1 es a select grop of people (about 2500), whereas File 2 is the
general database (about 30thou)

They're asking me to match the data, in order to end up with the
coresponding date they joined the company in column B on File 1 (obtained
from column D on file 2), how to do it? taking in account that File 1 is
outdated and some people might not be in file 2.

I would really appreciate any help you might give me.


Gloria