Merger Two Data Sheet

[Charles]

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

[Earl Kiosterud]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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
>
>
>
>
>
>
>