| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two worksheets that have a common field
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging two worksheets that have a common field
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "lmirrim" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging two worksheets that have a common field
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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "lmirrim" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging two worksheets that have a common field
Thank you. It worked very well!
"Bernard Liengme" wrote: 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 Jones Alice 902-555-1212 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 Smith Joyce 11 Mr Newton Smith Albert 123-456-789 (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 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 "lmirrim" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "lmirrim" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to combine two worksheets into one. Only ONE column is common. | Excel Discussion (Misc queries) | |||
| combining two spreadsheets with a common field | Excel Discussion (Misc queries) | |||
| Merge rows from 2 XLS files using a common key field. | Excel Discussion (Misc queries) | |||
| Add data based on a common field | Excel Worksheet Functions | |||
| Compare two worksheets and identify common entries | Excel Discussion (Misc queries) | |||
Linear Mode
