Prev: How to determine the value?
Next: Converting xls to ofx
From: gloriamar on 6 Jun 2010 04:13
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
From: Dave Peterson on 6 Jun 2010 06:43
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
From: Ms-Exl-Learner on 6 Jun 2010 06:54
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
Cells>>Number>>Category>>Date>>Type>>Select your desired Date Format and give
Ok.

For Converting the formula result to Values:-
Select and copy the Entire B Column and Do right click>>Paste
Special>>Select Values Option Button and give Ok…

Have a look in the below links for more details about Vlookup:-
http://office.microsoft.com/en-us/excel/hp052093351033.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 it's 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
From: Tom-S on 6 Jun 2010 06:54
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
 | 
Pages: 1
Prev: How to determine the value?
Next: Converting xls to ofx