Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
How can I merge two Excel worksheets from the same
workbook, and link them on a common field? for
instance, I have a parent roster and a student roster on
two different worksheets. I want to link them together on
the last name so I can create a master list. Hw can I do
this? Thanks.
You need to explain what you are trying to do in a little more detail.
"I want to link them together on the last name so I can create a master list." doesn't tell us much.
Perhaps an example or two of input data and the desired output would help.
0 
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
0
For example:
Student worksheet:
Last name, firstname, grade, teacherParent worksheet:
last name, first name, contact phone, email addressI want to create a master list that contains all of the above
fields on one worksheet.I know an easy way to do this in a programming language
like SAS, but there must be someone to do it simply in
Excel. Any ideas?
0
Sounds like VLOOKUP might work, but how will you deal with 2 families that have the same last name or families that have more than 1 child?
As per the Excel help files, the syntax is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Normally you would use the col_index_num of the table_array to pull the grade, teacher, etc. based on the lookup of the last name.
The problem is going to be in those situations where you have 2 or more families with the same last name or more than one kid per family.
VLOOKUP is going to find the first occurance of the last name and ignore all others.
That problem is going to exist even if you code something in VBA, unless (in both cases) you can create a column that contains something unique to each family to key off of.
Based on the data below, which kids belong to which parent and vice versus?
Student worksheet:
Smith, Tom, 5, Mr. Magoo
Smith, Sue, 6, Mr. Peabody
Smith, Bobby, 6, Mr. PeabodyParent worksheet:
Smith, Fred, 213-456-7890, 123 One St
Smith, Barb, 213-987-6543, 456 Two StYou need a "family key" as a starter and then we can take it from there.
0
The more I think about it, the more I think you are going to neeed some VBA code anyway so that you can pull data for more than one child in the same family.
Even with a "family key", VLOOKUP is only going to find the first occurance of that key in the table_array.
VBA is the only way I know of to find multiple occurances of the same lookup_value (the family key) because it can loop through a range looking for each occurance.
The next issue becomes formatting:
One family might have 1 kid, another (mine!) might have 4. How do you plan to format the sheet to place a different number of kids with each family. All of this has to be dealt with within the code, so we'd need to know that before the code could be written.
0
I'm working with data that has been given to me. I didn;t have a
chance to create these worksheets myself. As they are
formatted now, each child appears as a separate record. As in
my family, I have 4 kids as well, each child appears on his/her
own line.
0
In your example you gave the fields as:
Student worksheet:
Last name, firstname, grade, teacherIs that all the info you have?
Because if you have the Students Home phone number,
for Emergency purposes, you could use that as the "Family Key" to cross reference Student to Parent.
0
In response # 7 you told us a little bit about how the data was laid out in the sheet, but you didn't respond to my comments about how you plan to deal with multiple kids in the same family or families with the same last name.
If you are looking at the data that you have now and have no idea which Smith kid belongs to which Smith parent, then merging the sheets isn't going to be anything but an exercise in data manipulation.
I believe they refer to that as garbage in, garbage out.
0
Aside from assigning each child a unique ID number that matches the parents ID number, I'm not sure how to do this. I think I'd have to figure out how many multiple child families there are and assign the parents a unique ID for each child. Mulriple records on their part. This might be easier to do with a statistical package like SAS or something similar. I'll play around with it and see how it goes. Thank you so much for all of your help.
0
re: Aside from assigning each child a unique ID number that matches the parents ID number,
That doesn't make sense.
If you assign each child a unique ID then how would each of them match the parent's ID?
I would think that you would want a unique ID for each family not each child.
Then you would have something to key off of and a VBA macro, perhaps using .Find to locate each occurrence of the family ID, could be used.
0
My opinion:
Don't overly complicate this.
Aside from assigning each child a unique ID number that matches the parents ID number
Use the home or emergency contact phone number as your key.
Each child should have an Emergency Contact number recorded somewhere and it links you too the Parents.The only time you may have a problem is if the parents are divorced and have some type of joint custody arrangement.
0
re: Each child should have an Emergency Contact number recorded somewhere and it links you too the Parents.
This is hit or miss at best.
re The only time you may have a problem is if the parents are divorced
Only? Divorce is not the only reason for emergency (or even home) phones not to match.
Parents who can't be contacted at work, grandparents as emergency contacts, cell phones in one database, home phones in another, no phone number in one or the other database. I could go on, but I think you see the point.
If you are very lucky, you might be able to use phone numbers as the key, but I think the safest bet is to actually assign a family ID - that the DBA has control over - so that if a family moves, or a cell phone plan is changed, or a divorce occurs, the key does not get messed up.
0
You are correct in your assessment that a Family ID is the ideal,
but for now he’s just trying to combine two work sheets with the info that is available.We know he has a parent phone number.
We don’t even know if he as an Emergency Contact phone number for the student list.My point was that if he as phone numbers for both parents and students in his current sheets, then that would be the simplest route to match Student with Parent and combine the sheets.
Once he has his two sheets combined, he can than go on to creating a Family ID system thus making life simpler.
But for the current problem of combing the two sheets, if he has the information, I think the phone number route will yield the best results.
0
I assume you mean "if he has matching phone numbers for both parents and students..."
If wishes were horses, even beggers could ride.
0
Believe it or not, I did do the phone number trick earlier today and it worked. Each child, even multiple kids form the same family, now has a complete record pulling data from multiple sheets using the VLOOKUP function.
Thanks for all of your help.
0
I'm curious.
How did you get VLOOKUP to pull different sets of data from the same sheet using the same phone number ?
Pulling the same data multiple times, I can see, but how did you get it to pull more than one set of data by looking up the same phone number?
0
The data that was given to me was set up with multiple parent
record for the same family: 2 kids, 2 parent record, 3 kids 3
parent records etc. In the end, I've achieved the result I
wanted. Thanks.
0  |
 i want the exact software...
|
how to delete a Category ...
|
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
