Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I match data from two separate files in excel
I am trying to analyse financial data for two different periods. Period one
will have say, 10 people with a value. Period 2 will have say 14 people with a value. However some people in period 1 will have no value in period 2 and also some people in period 2 were new in period 1 and so did not exist in period 1. If I have two separate files for each period (i.e. period 1 has people and a value and similarly for period 2), how can I create a list with column 1 being for all people form both periods and showing their values for periods 1 and 2 in columns 2 and 3 therefore enabling me to anylyse any trends |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I match data from two separate files in excel
There are a number of methods depending on how often you want to do this, how much data, what you are going to do with the results. Options include: 1. (neat and good for large volumes of data) set up the spreadsheets as linked tables in access and then use a query to join the information together 2. like 1 but only for the technical do the same as 1 but useing ado in macro to populate another sheet (or workbook) 3. Simply make a list of all the employees on a new sheet. use vlookup (this site has plenty of info on how to use vlookup) to pull in data for the other columns. 4. moderately complex (but all excel) a macro to merge the two sets of data Let us know what you think and maybe you will get more detailed help regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=561080 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I match data from two separate files in excel
Can you elaborate on option 1 below.
I have a similar problem; let's say I have these tables: Date FieldA 1/1/2006 3 1/2/2006 4 Date FieldB 1/1/2006 10 1/3/2006 15 I want to end up with: Date FieldA FieldB 1/1/2006 3 10 1/2/2006 4 NULL 1/3/2006 NULL 15 p.s. NULLs could be 0 rather; that would also be OK I tried Access, but it does not seem to work for me!? I added 2 linked-excel tables for the above 2 tables. But then, I am not sure how to do the joing? i.e. "then use a query to join the information...": i.e. how is that done? Thank you, Nicolas "tony h" wrote: There are a number of methods depending on how often you want to do this, how much data, what you are going to do with the results. Options include: 1. (neat and good for large volumes of data) set up the spreadsheets as linked tables in access and then use a query to join the information together 2. like 1 but only for the technical do the same as 1 but useing ado in macro to populate another sheet (or workbook) 3. Simply make a list of all the employees on a new sheet. use vlookup (this site has plenty of info on how to use vlookup) to pull in data for the other columns. 4. moderately complex (but all excel) a macro to merge the two sets of data Let us know what you think and maybe you will get more detailed help regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=561080 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I match data from two separate files in excel
I am also looking for a solution to do the same thing as mentioned below.
Please post the solution if you found one. Regards Rajula "Nicolas" wrote: Can you elaborate on option 1 below. I have a similar problem; let's say I have these tables: Date FieldA 1/1/2006 3 1/2/2006 4 Date FieldB 1/1/2006 10 1/3/2006 15 I want to end up with: Date FieldA FieldB 1/1/2006 3 10 1/2/2006 4 NULL 1/3/2006 NULL 15 p.s. NULLs could be 0 rather; that would also be OK I tried Access, but it does not seem to work for me!? I added 2 linked-excel tables for the above 2 tables. But then, I am not sure how to do the joing? i.e. "then use a query to join the information...": i.e. how is that done? Thank you, Nicolas "tony h" wrote: There are a number of methods depending on how often you want to do this, how much data, what you are going to do with the results. Options include: 1. (neat and good for large volumes of data) set up the spreadsheets as linked tables in access and then use a query to join the information together 2. like 1 but only for the technical do the same as 1 but useing ado in macro to populate another sheet (or workbook) 3. Simply make a list of all the employees on a new sheet. use vlookup (this site has plenty of info on how to use vlookup) to pull in data for the other columns. 4. moderately complex (but all excel) a macro to merge the two sets of data Let us know what you think and maybe you will get more detailed help regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=561080 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I match data from two separate files in excel
Hi, All
I am looking just for the same solution Did you get it and woul be so nice to share with me? Thanks so much "Rajula" wrote: I am also looking for a solution to do the same thing as mentioned below. Please post the solution if you found one. Regards Rajula "Nicolas" wrote: Can you elaborate on option 1 below. I have a similar problem; let's say I have these tables: Date FieldA 1/1/2006 3 1/2/2006 4 Date FieldB 1/1/2006 10 1/3/2006 15 I want to end up with: Date FieldA FieldB 1/1/2006 3 10 1/2/2006 4 NULL 1/3/2006 NULL 15 p.s. NULLs could be 0 rather; that would also be OK I tried Access, but it does not seem to work for me!? I added 2 linked-excel tables for the above 2 tables. But then, I am not sure how to do the joing? i.e. "then use a query to join the information...": i.e. how is that done? Thank you, Nicolas "tony h" wrote: There are a number of methods depending on how often you want to do this, how much data, what you are going to do with the results. Options include: 1. (neat and good for large volumes of data) set up the spreadsheets as linked tables in access and then use a query to join the information together 2. like 1 but only for the technical do the same as 1 but useing ado in macro to populate another sheet (or workbook) 3. Simply make a list of all the employees on a new sheet. use vlookup (this site has plenty of info on how to use vlookup) to pull in data for the other columns. 4. moderately complex (but all excel) a macro to merge the two sets of data Let us know what you think and maybe you will get more detailed help regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=561080 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from multiple excel files. | Excel Discussion (Misc queries) | |||
combining data from several excel files into one file | Excel Discussion (Misc queries) | |||
How do I update data in 3 related files in Excel? | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |