James Townsley
asked on
Combining Data between multiple excel workbooks
Hello, I have 3 excel workbooks containing Various student test score info. One File contains ACT data one has SAT data and the other PSAT data.
The first 3 columns in all 3 files are identical
Student ID, Last_Name, First_Name.
The remaining columns in all files are different as they contain the Test Date, Test Type, Score type and Test Score for each individual test. So each worksheet looks like this
ID, Fname, Lname, Test_Date, Test_Type, Score_Type, Score Score_Type Score
123 Jim Bean 6-15-15 ACT Math 23 Reading 21
What i need to do is create one worksheet that combines the data from all 3 workbooks. so the results would look like this
ID FNAME LNAME ACT_DATE ACT_Score _TYPE ACT_SCORE SAT_DATE SAT_Score_Type SAT_Score
123 Jim Bean 6-15-15 Math 23 8-15-16 Reading 750
I would like one row per student with columns containing the score information from the other workbooks.
The ACT and SAT scores are stored in separate workbooks. Is this possible or am i out of my mind? Any help would be greatly appreciated.
The first 3 columns in all 3 files are identical
Student ID, Last_Name, First_Name.
The remaining columns in all files are different as they contain the Test Date, Test Type, Score type and Test Score for each individual test. So each worksheet looks like this
ID, Fname, Lname, Test_Date, Test_Type, Score_Type, Score Score_Type Score
123 Jim Bean 6-15-15 ACT Math 23 Reading 21
What i need to do is create one worksheet that combines the data from all 3 workbooks. so the results would look like this
ID FNAME LNAME ACT_DATE ACT_Score _TYPE ACT_SCORE SAT_DATE SAT_Score_Type SAT_Score
123 Jim Bean 6-15-15 Math 23 8-15-16 Reading 750
I would like one row per student with columns containing the score information from the other workbooks.
The ACT and SAT scores are stored in separate workbooks. Is this possible or am i out of my mind? Any help would be greatly appreciated.
D Patel
Can you post some sample data?
ASKER
Sure, See the attached document. This document contains two worksheets, one with PSAT test score data and one with SAT score data.
I need to combine the two files where all columns (and subsequent data) from each worksheet are on one worksheet.
-PSAT-Example-File.xls
I need to combine the two files where all columns (and subsequent data) from each worksheet are on one worksheet.
-PSAT-Example-File.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Mike in IT It looks like this will actually work.. :-)
I need will need to extrapolate this out to multiple test files SAT ACT PSAT SSAT IOWA etc.. and I need a little more clarification on exactly what the vlookup code is doing. particularly the section after Sheet1! (bold) (VLOOKUP(A3,[SAT.xlsx]Shee
also,
This code worked beautifully in the example files. In the example files however, the students are the same exactly the same. This won't be the case with all files, does this matter?
For example I will have a master file with all students in grades 3 - 12. This file will contain a Student ID column, and columns for every test type.
ID ACT Score SAT Score Iowa Score PSAT Score SSAT Score etc...
Some Students that are listed in the master file, may not exist in one of the other test files that I'm pulling data from. So, a student might have taken the SAT and will have a record in the SAT excel workbook, but that student may not have taken the SSAT and therefore will not have a record in SSAT workbook Will this be a problem with this VLOOKUP code?
From your example file, it appears that if the student does not exists in one of the other files, then it will enter "No SSAT Score" is this correct? If so, that would be perfect.
I need will need to extrapolate this out to multiple test files SAT ACT PSAT SSAT IOWA etc.. and I need a little more clarification on exactly what the vlookup code is doing. particularly the section after Sheet1! (bold) (VLOOKUP(A3,[SAT.xlsx]Shee
also,
This code worked beautifully in the example files. In the example files however, the students are the same exactly the same. This won't be the case with all files, does this matter?
For example I will have a master file with all students in grades 3 - 12. This file will contain a Student ID column, and columns for every test type.
ID ACT Score SAT Score Iowa Score PSAT Score SSAT Score etc...
Some Students that are listed in the master file, may not exist in one of the other test files that I'm pulling data from. So, a student might have taken the SAT and will have a record in the SAT excel workbook, but that student may not have taken the SSAT and therefore will not have a record in SSAT workbook Will this be a problem with this VLOOKUP code?
From your example file, it appears that if the student does not exists in one of the other files, then it will enter "No SSAT Score" is this correct? If so, that would be perfect.
To answer your follow-up questions:
VLOOKUP(A3,[SAT.XLSX]Sheet
What this does:
A3 = this is what is being looked for, in this case, the student id
[SAT.XLSX]Sheet1! = this is selecting the workbook and sheet
$A2:$I500 = this is selecting the range from A2 - I500. VLOOKUP will check the first column A2:A500 for the StudentID, all the columns are indexed starting with 1. So column A is 1, B is 2 so on and so forth.
2 = this tells VLOOKUP what to return, in this case, the value in the second column (B) which is the first name of the student
FALSE = this tells VLOOKUP that it doesn't have to be an exact match, just very close. It makes it easier and more likely to find what you are looking for in this case. It will ignore extra space before and after the ID
If the student ID is not in one of the files: that is why I surrounded the VLOOKUP with the IFERROR this means that when VLOOKUP doesn't return anything IFERROR is false and the second value is returned. In this case, I put "No SAT Score".
There are some problems, such as when you have the studentID, but then the scores are all blank, VLOOKUP will return the blanks because it found the ID. There may be other problems as well, but that was one that I found.
Hope that answers all your questions.
VLOOKUP(A3,[SAT.XLSX]Sheet
What this does:
A3 = this is what is being looked for, in this case, the student id
[SAT.XLSX]Sheet1! = this is selecting the workbook and sheet
$A2:$I500 = this is selecting the range from A2 - I500. VLOOKUP will check the first column A2:A500 for the StudentID, all the columns are indexed starting with 1. So column A is 1, B is 2 so on and so forth.
2 = this tells VLOOKUP what to return, in this case, the value in the second column (B) which is the first name of the student
FALSE = this tells VLOOKUP that it doesn't have to be an exact match, just very close. It makes it easier and more likely to find what you are looking for in this case. It will ignore extra space before and after the ID
If the student ID is not in one of the files: that is why I surrounded the VLOOKUP with the IFERROR this means that when VLOOKUP doesn't return anything IFERROR is false and the second value is returned. In this case, I put "No SAT Score".
There are some problems, such as when you have the studentID, but then the scores are all blank, VLOOKUP will return the blanks because it found the ID. There may be other problems as well, but that was one that I found.
Hope that answers all your questions.
ASKER
Thanks Mike. I think I can work with this. I appreciate your help.
ASKER
This really set me out in the right direction with what I was trying to accomplish. Thank a million.
I'm glad that I could help, you are welcome.
Correction on your explanation:
FALSE tells the VLOOKUP to find an EXACT match. TRUE will find a close match but not greater than when sorted in order.
If the lookup value includes leading or trailing spaces, they will not be ignored, they will be included as part of the lookup value. If one file has spaces and the other one doesn't they will not match. If that could happen, you would need to use the TRIM function to remove surplus spaces.
This is correct but worth noting that the Indexing is relative to the first column of the lookup range. If the data was in columns C to K, column C would be column 1 because it is the first column of the range, therefore column D would be column 2.
FALSE = this tells VLOOKUP that it doesn't have to be an exact match, just very close. It makes it easier and more likely to find what you are looking for in this case. It will ignore extra space before and after the IDThis is Wrong!
FALSE tells the VLOOKUP to find an EXACT match. TRUE will find a close match but not greater than when sorted in order.
If the lookup value includes leading or trailing spaces, they will not be ignored, they will be included as part of the lookup value. If one file has spaces and the other one doesn't they will not match. If that could happen, you would need to use the TRIM function to remove surplus spaces.
$A2:$I500 = this is selecting the range from A2 - I500. VLOOKUP will check the first column A2:A500 for the StudentID, all the columns are indexed starting with 1. So column A is 1, B is 2 so on and so forth.
2 = this tells VLOOKUP what to return, in this case, the value in the second column (B) which is the first name of the student
This is correct but worth noting that the Indexing is relative to the first column of the lookup range. If the data was in columns C to K, column C would be column 1 because it is the first column of the range, therefore column D would be column 2.
@RobHenson You are absolutely correct. I always seem to get the True/False backward for VLOOKUP. I know what it does but can never seem to remember why and reading it in excel (or above) reminds me again. Thanks for that.
About the trailing and leading spaces, I had thought (apparently incorrectly) that they were ignored when not looking for an exact match? You are probably right and that would differently make using TRIM necessary as you said.
I thought I had been clear about the indexes, thank you for clearing it up. I often forget that just because I'm thinking of it doesn't mean that everyone else will as well.
Thanks for the help in clarifying my explanation, I'd hate to steer someone wrong.
About the trailing and leading spaces, I had thought (apparently incorrectly) that they were ignored when not looking for an exact match? You are probably right and that would differently make using TRIM necessary as you said.
I thought I had been clear about the indexes, thank you for clearing it up. I often forget that just because I'm thinking of it doesn't mean that everyone else will as well.
Thanks for the help in clarifying my explanation, I'd hate to steer someone wrong.
ASKER
Thanks Guys.