Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cross referencing data | Excel Discussion (Misc queries) | |||
How to cross-multiply text data in excel?? | Excel Discussion (Misc queries) | |||
Cross-Referencing Data | Excel Discussion (Misc queries) | |||
Capture cross-section data | Excel Discussion (Misc queries) | |||
cross referenceing data in excel | Excel Discussion (Misc queries) |