Merger Two Data Sheet
 

Hi All:

I need to merge two variables but I could not find any instructions on this
issue.

I have a data sheet 1 like this:

Course Hours
Acc100 3.0
Acc122 3.0
Mar095 3.5
Bus100 4.0
Bus101 3.0

I have data sheet 2 like:

Course Instructor
Acc100 Charles
Acc101 David
Acc122 James
Mar095 Mary
Mar100 Greg
Mar155 Sharon
Bus100 Lisa
Bus101 Sue
Bus105 Tom


Data sheet 1 and data sheet 2 have the same variable Course. Course contains
much more records in data sheet 2 and all records of Course in data sheet 1
are included in Course in data sheet 2. Now I want to merge these two sheets
to create file like:

Course Instructor Hours
Acc100 Charles 3.0
Acc122 James 3.0
Mar095 Mary 3.5
Bus100 Lisa 4.0
Bus101 Sue 3.0


Is there is anyone who could tell me the procefures I can accomplish this
goal? Any suggestions and advices are greatly appreciated.

Charles

Charles,

You need to use a VLOOKUP in the first data sheet.

=VLOOKUP(A2, Table,2,FALSE)
=VLOOKUP(A2, 'Sheet 2'!A1:B65536, 2, FALSE)

This would go in the next column of the sheet 1 table. Table is a defined
name for the table in sheet 2, and does not include the heading row (though
in this case it would do no harm). A2 is the first Course (Acc100) in the
table in Sheet 1.

In database parlance, you've joined the "many" table (sheet 1) with the
"one" table" (sheet 2). Access is a good application for this kind of
stuff.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Charles" wrote in message
...
Hi All:

I need to merge two variables but I could not find any instructions on
this
issue.

I have a data sheet 1 like this:

Course Hours
Acc100 3.0
Acc122 3.0
Mar095 3.5
Bus100 4.0
Bus101 3.0

I have data sheet 2 like:

Course Instructor
Acc100 Charles
Acc101 David
Acc122 James
Mar095 Mary
Mar100 Greg
Mar155 Sharon
Bus100 Lisa
Bus101 Sue
Bus105 Tom


Data sheet 1 and data sheet 2 have the same variable Course. Course
contains
much more records in data sheet 2 and all records of Course in data sheet
1
are included in Course in data sheet 2. Now I want to merge these two
sheets
to create file like:

Course Instructor Hours
Acc100 Charles 3.0
Acc122 James 3.0
Mar095 Mary 3.5
Bus100 Lisa 4.0
Bus101 Sue 3.0


Is there is anyone who could tell me the procefures I can accomplish this
goal? Any suggestions and advices are greatly appreciated.

Charles

Hi Earl:

Thanks a lot for your answering my question. But I tried to test your
method and found I still can not understand what you told me. Specifically,
I have two more question regarding your solution:

1. What is Table? Does it mean the range of data?

2. After I typed in the first formula, Excel will not let me type in the
second formular.

To make things simple, is there any way you could give me an example. Thank
you very much again.

Charles




"Charles" wrote:

Hi All:

I need to merge two variables but I could not find any instructions on this
issue.

I have a data sheet 1 like this:

Course Hours
Acc100 3.0
Acc122 3.0
Mar095 3.5
Bus100 4.0
Bus101 3.0

I have data sheet 2 like:

Course Instructor
Acc100 Charles
Acc101 David
Acc122 James
Mar095 Mary
Mar100 Greg
Mar155 Sharon
Bus100 Lisa
Bus101 Sue
Bus105 Tom


Data sheet 1 and data sheet 2 have the same variable Course. Course contains
much more records in data sheet 2 and all records of Course in data sheet 1
are included in Course in data sheet 2. Now I want to merge these two sheets
to create file like:

Course Instructor Hours
Acc100 Charles 3.0
Acc122 James 3.0
Mar095 Mary 3.5
Bus100 Lisa 4.0
Bus101 Sue 3.0


Is there is anyone who could tell me the procefures I can accomplish this
goal? Any suggestions and advices are greatly appreciated.

Charles