If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 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 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 |
#4
|
|||
|
|||
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!C130000"),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 C130000. 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 |
Thread Tools | |
Display Modes | |
|
|