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?