Merging two worksheets that have a common field

[lmirrim]

How do I go about merging two Excel 2007 worksheets (in the same workbook)
into one master list? I want to connect them using a common field, say, LAST
NAME. How can I do this to create one master worksheet? Thanks.

 

[Bernard Liengme]

Have a look at the VLOOKUP function in Help
Then come back with more details of how the data is set out in you two files
your two files
best wishes

 

[lmirrim]

I have two worksheets: One with a students first and last name
and one with the parents first and last name. I want to link the
two worksheets together by matching the last names.

For example:

Student worksheet:
Last name, firstname, grade, teacher

Parent worksheet:
last name, first name, contact phone, email address


I want to create a master list that contains all of the above
fields on one worksheet. One record per student.

I know a way to do this in a program like SAS, but there must be some way
to do it simply in
Excel. Any ideas?

 

[Bernard Liengme]

On Sheet1 beginning in A1 I have these two student records
Jones Brian 10 Mr Boyle
Smith Joyce 11 Mr Newton
On Sheet 2 I have these two parent records also beginning in A1
Smith Albert 123-456-789 (e-mail address removed)
Jones Alice 902-555-1212 (e-mail address removed)

I have put them is different order to show the order is not an issue
In D1 of Sheet 1 I have entered the formula
=VLOOKUP($A1,Sheet2!$A$1:$D$100,1,FALSE)
In E2 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,2,FALSE)
Note how we go from 1 to 2 for the third arugment - this is the column we
are picking up
So in F1 and G1 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,3,FALSE)
=VLOOKUP($A1,Sheet2!$A$1:$D$100,4,FALSE)
You can enter the first, drag across and edit to fix the third argument
(there is another way but not required with just 4 four fields)

Note the D100 --- I am pretending I have 100 parent records (make as big as
you need)
I selected D1:G1 on the student sheet and dragged down the row to get
Jones Brian 10 Mr Boyle Jones Alice 902-555-1212 (e-mail address removed)
Smith Joyce 11 Mr Newton Smith Albert 123-456-789 (e-mail address removed)
(Shame we get a scramble when we copy form Excel to email client - but on
the worksheet the fields align)

If the parents are in a different workBOOK, use something like
=VLOOKUP($A1,'[Parents 2009.xlsx]Sheet1'!$A$1:$D$100,1,FALSE)
and make sure that file is open

In the area I live many residents are descended from Scottish immigrants who
had to leave Scotland in the "clearances". So some 15% are called MacDonald
and other 5% are Chisholms. This method would fail locally!! You will
always get the first name that has a match in the two lists.

When I typed in something like (e-mail address removed) I got a mail_to hyperlink.
This is not preserved with the VLOOKUP

best wishes

 

[lmirrim]

Thank you. It worked very well!

On Sheet1 beginning in A1 I have these two student records
Jones Brian 10 Mr Boyle
Smith Joyce 11 Mr Newton
On Sheet 2 I have these two parent records also beginning in A1
Smith Albert 123-456-789 (e-mail address removed)
Jones Alice 902-555-1212 (e-mail address removed)

I have put them is different order to show the order is not an issue
In D1 of Sheet 1 I have entered the formula
=VLOOKUP($A1,Sheet2!$A$1:$D$100,1,FALSE)
In E2 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,2,FALSE)
Note how we go from 1 to 2 for the third arugment - this is the column we
are picking up
So in F1 and G1 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,3,FALSE)
=VLOOKUP($A1,Sheet2!$A$1:$D$100,4,FALSE)
You can enter the first, drag across and edit to fix the third argument
(there is another way but not required with just 4 four fields)

Note the D100 --- I am pretending I have 100 parent records (make as big as
you need)
I selected D1:G1 on the student sheet and dragged down the row to get
Jones Brian 10 Mr Boyle Jones Alice 902-555-1212 (e-mail address removed)
Smith Joyce 11 Mr Newton Smith Albert 123-456-789 (e-mail address removed)
(Shame we get a scramble when we copy form Excel to email client - but on
the worksheet the fields align)

If the parents are in a different workBOOK, use something like
=VLOOKUP($A1,'[Parents 2009.xlsx]Sheet1'!$A$1:$D$100,1,FALSE)
and make sure that file is open

In the area I live many residents are descended from Scottish immigrants who
had to leave Scotland in the "clearances". So some 15% are called MacDonald
and other 5% are Chisholms. This method would fail locally!! You will
always get the first name that has a match in the two lists.

When I typed in something like (e-mail address removed) I got a mail_to hyperlink.
This is not preserved with the VLOOKUP

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I have two worksheets: One with a students first and last name
and one with the parents first and last name. I want to link the
two worksheets together by matching the last names.

For example:

Student worksheet:
Last name, firstname, grade, teacher

Parent worksheet:
last name, first name, contact phone, email address


I want to create a master list that contains all of the above
fields on one worksheet. One record per student.

I know a way to do this in a program like SAS, but there must be some way
to do it simply in
Excel. Any ideas?