| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MERGE 2 FILES
I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE,
INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
The common field appears to be the COURSE field. This means you can
use that in a VLOOKUP function, which retrieves data from a range based on a matching field. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
If you really must do this and you don't mind playing with a little SQL code,
Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Good point....
I puzzled a bit on that one, but reasoned that the same course could be taught by different instructors at different dates. Consequently, the course name and the course date would have to be taken into consideration. Intro to Excel...Instructor1.......10/13/2005 Intro to Excel...Instructor2.......10/25/2005 If the OP is willing to put both tables in the same workbook and the tables aren't inordinately large, formulas could be used. Probably the typical array formulas that we use for multiple criteria. Otherwise, a little SQL can go a long way with minimal update effort (Click/data refresh). €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Dave O" wrote: The common field appears to be the COURSE field. This means you can use that in a VLOOKUP function, which retrieves data from a range based on a matching field. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Thank you Dave, Ron for the help. Will try it today.
"Ron Coderre" wrote: Good point.... I puzzled a bit on that one, but reasoned that the same course could be taught by different instructors at different dates. Consequently, the course name and the course date would have to be taken into consideration. Intro to Excel...Instructor1.......10/13/2005 Intro to Excel...Instructor2.......10/25/2005 If the OP is willing to put both tables in the same workbook and the tables aren't inordinately large, formulas could be used. Probably the typical array formulas that we use for multiple criteria. Otherwise, a little SQL can go a long way with minimal update effort (Click/data refresh). €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Dave O" wrote: The common field appears to be the COURSE field. This means you can use that in a VLOOKUP function, which retrieves data from a range based on a matching field. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Hi Ron,
When I click on OK I'm getting this msg "SQL Query cannot be represented graphically, continue?" I clicked yes and get this msg "Could not add the table '(SELECT'." Anything wrong with my query below? Thanks Ron. SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `N:\InfoMgmt\waFILES\OTHERS2\course_students`.rngN ameCourse) rngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `N:\InfoMgmt\waFILES\OTHERS2\course_INSTRUCTOR'.rn gCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseIntr.COURSE AND rngNameCourse.DATE = rngCourseIntr.DATE "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
It looks like you have a couple typos in the WHERE clause:
rngCourseIntr should be rngCourseInstr (missing an "s" in Instr in both statements) Does that fix it? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: Hi Ron, When I click on OK I'm getting this msg "SQL Query cannot be represented graphically, continue?" I clicked yes and get this msg "Could not add the table '(SELECT'." Anything wrong with my query below? Thanks Ron. SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `N:\InfoMgmt\waFILES\OTHERS2\course_students`.rngN ameCourse) rngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `N:\InfoMgmt\waFILES\OTHERS2\course_INSTRUCTOR'.rn gCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseIntr.COURSE AND rngNameCourse.DATE = rngCourseIntr.DATE "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Ready for the easier way to do this? How about with no SQL to type?
-DataGet External Data \Use Excel Files \Select the Course_Students.xls file \Select the rngNameCourse range \Click [Next] until the last screen and edit the query (so far...same as before) Once MS Query displays the data -Click the Add Table(s) button -At the bottom of the dialog, click the Workbook dropdown \Select the Course_Instructors.xls file (it must be located in the same directory as the first file) \Select the rngCourseInstr range and click [OK] Now you should see 2 tables displayed. -Drag the COURSE field from one table to the COURSE field on the other table -Drag the DATE field from one table to the DATE field on the other table -Double clidk on the INSTRUCTOR field (it should now appear in the returned data) -Click the Query Now button (!) to get the latest data ....and if you check the SQL, it's even more compact than the code I threw together. -Click the Return Data to Excel button Done Easier to do, yes? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Ron Coderre" wrote: It looks like you have a couple typos in the WHERE clause: rngCourseIntr should be rngCourseInstr (missing an "s" in Instr in both statements) Does that fix it? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: Hi Ron, When I click on OK I'm getting this msg "SQL Query cannot be represented graphically, continue?" I clicked yes and get this msg "Could not add the table '(SELECT'." Anything wrong with my query below? Thanks Ron. SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `N:\InfoMgmt\waFILES\OTHERS2\course_students`.rngN ameCourse) rngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `N:\InfoMgmt\waFILES\OTHERS2\course_INSTRUCTOR'.rn gCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseIntr.COURSE AND rngNameCourse.DATE = rngCourseIntr.DATE "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Thanks for the big help, I've been trying to learn how to work on SQL from
Excel and you just showed me how and the query works too. Glad that the error is only the missing "s" in intr. That means that my SQL skill from a long time ago is still there. Now I have to try the VLOOKUP since they told me that this is also very powerful. Thanks so much Ron. "Ron Coderre" wrote: It looks like you have a couple typos in the WHERE clause: rngCourseIntr should be rngCourseInstr (missing an "s" in Instr in both statements) Does that fix it? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: Hi Ron, When I click on OK I'm getting this msg "SQL Query cannot be represented graphically, continue?" I clicked yes and get this msg "Could not add the table '(SELECT'." Anything wrong with my query below? Thanks Ron. SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `N:\InfoMgmt\waFILES\OTHERS2\course_students`.rngN ameCourse) rngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `N:\InfoMgmt\waFILES\OTHERS2\course_INSTRUCTOR'.rn gCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseIntr.COURSE AND rngNameCourse.DATE = rngCourseIntr.DATE "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
It works fine, however, what if I need the file3 to contain ALL file1 (like
an update only) even if there is a match or unmatch. Right now, the Query will only show me matched items in file3. Thanks again. "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MERGE 2 FILES
Sorry for the delay....
"will A" wrote: It works fine, however, what if I need the file3 to contain ALL file1 (like an update only) even if there is a match or unmatch. Right now, the Query will only show me matched items in file3. Thanks again. THAT..MS Query does not do so automatically. You're asking it to do an outer join on 2 fields: Not allowed in MS Quer, but Easily done in Access. Anyway...since you asked for it...I worked around MS Queries limitation by creating a virtual 'helper field' for each table, much like we use in Excel, except these are built at runtime in SQL. The 'helper field' (CourseID) is a concatenation of the COURSE and DATE fields. Here's the SQL: SELECT Students.FNAME, Students.COURSE, Students.DATE, Instructors.INSTRUCTOR FROM {oj (SELECT FNAME, LNAME, COURSE, DATE, COURSE & '_' & FORMAT(DATE,'YYYYMMDD') AS CourseID FROM `C:\Course_Students`.rngNameCourse) Students LEFT OUTER JOIN (SELECT INSTRUCTOR, COURSE & '_' & FORMAT(DATE,'YYYYMMDD') AS CourseID FROM `C:\Course_Instructors`.rngCourseInstr) Instructors ON Students.CourseID = Instructors.CourseID} To test, go to the Instructors list and delete some rows. When you run the query, you should see students taking courses with no instructors. Does that solve your problem? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Ron Coderre" wrote: If you really must do this and you don't mind playing with a little SQL code, Here's how I did it using MS Query.... Using your Column Headings in 2 different Excel workbooks, Course_Students.xls Range named: rngNameCourse Referring to this list: FNAME LNAME COURSE DATE First1 Last1 Course1 31-Jan First2 Last2 Course1 1-Feb First3 Last3 Course1 2-Feb First4 Last4 Course1 3-Feb First5 Last5 Course1 4-Feb First6 Last6 Course1 5-Feb First7 Last7 Course1 6-Feb First8 Last8 Course1 7-Feb First9 Last9 Course1 8-Feb First10 Last10 Course1 9-Feb First1 Last1 Course2 10-Feb First2 Last2 Course2 11-Feb First3 Last3 Course2 12-Feb First4 Last4 Course2 13-Feb First5 Last5 Course2 14-Feb First6 Last6 Course2 15-Feb First7 Last7 Course2 16-Feb First8 Last8 Course2 17-Feb First9 Last9 Course2 18-Feb First10 Last10 Course2 19-Feb Course_Instructor.xls Range named: rngCourseInstr Referring to this list: COURSE INSTRUCTOR DATE Course1 Al 31-Jan Course1 Ben 1-Feb Course1 Chuck 2-Feb Course1 Dave 3-Feb Course1 Ed 4-Feb Course1 Gus 5-Feb Course1 Harry 6-Feb Course1 Irv 7-Feb Course1 Jim 8-Feb Course1 Kara 9-Feb Course2 Al 10-Feb Course2 Ben 11-Feb Course2 Chuck 12-Feb Course2 Dave 13-Feb Course2 Ed 14-Feb Course2 Gus 15-Feb Course2 Harry 16-Feb Course2 Irv 17-Feb Course2 Jim 18-Feb Course2 Kara 19-Feb Next, in a new workbook.... DataGet External DataNew Database Query -Data Source is Excel File -Select the Course_Students.xls -Select the rngNameCourse -Continue through the screens, but at the end...opt to edit the query. Click the [SQL] button to see the SQL code. Edit the SQL to be this (edit to file location to match yours): SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE, rngCourseInstr.INSTRUCTOR FROM (SELECT rngNameCourse.FNAME, rngNameCourse.LNAME, rngNameCourse.COURSE, rngNameCourse.DATE FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse, (SELECT rngCourseInstr.DATE, rngCourseInstr.COURSE, rngCourseInstr.INSTRUCTOR FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr WHERE rngNameCourse.COURSE = rngCourseInstr.COURSE AND rngNameCourse.DATE = rngCourseInstr.DATE Click the [OK] button to see that data that will be returned If the data is ok...Click the button to return data to Excel Here's my returned data: COURSE FNAME LNAME DATE INSTRUCTOR Course1 First1 Last1 1/31/2005 0:00 Al Course1 First2 Last2 2/1/2005 0:00 Ben Course1 First3 Last3 2/2/2005 0:00 Chuck Course1 First4 Last4 2/3/2005 0:00 Dave Course1 First5 Last5 2/4/2005 0:00 Ed Course1 First6 Last6 2/5/2005 0:00 Gus Course1 First7 Last7 2/6/2005 0:00 Harry Course1 First8 Last8 2/7/2005 0:00 Irv Course1 First9 Last9 2/8/2005 0:00 Jim Course1 First10 Last10 2/9/2005 0:00 Kara Course2 First1 Last1 2/10/2005 0:00 Al Course2 First2 Last2 2/11/2005 0:00 Ben Course2 First3 Last3 2/12/2005 0:00 Chuck Course2 First4 Last4 2/13/2005 0:00 Dave Course2 First5 Last5 2/14/2005 0:00 Ed Course2 First6 Last6 2/15/2005 0:00 Gus Course2 First7 Last7 2/16/2005 0:00 Harry Course2 First8 Last8 2/17/2005 0:00 Irv Course2 First9 Last9 2/18/2005 0:00 Jim Course2 First10 Last10 2/19/2005 0:00 Kara That query joins the two tables (students, courses) to asign instructors for each course/date combination. Once back in Excel, you refresh the data by right-clicking on the queried data and selecting Refresh Data. I'd recommend duplicating my model to see how it works before you tackle it with your data. Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "will A" wrote: I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE, INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I merge information from sheets on other files into the ma. | Excel Worksheet Functions | |||
| merge data from 2 Excel files | Excel Worksheet Functions | |||
| how i can merge more than two excel files in one file. | New Users to Excel | |||
| How do I merge 2 Excel files, each with different names and data?. | Excel Discussion (Misc queries) | |||
| How do I merge two files in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
