How to line up data based on a single identifier?
March 24, 2011 1:46 PM Subscribe
MS Excel: All of my subjects took two tests and Scores 1 & 2 are in one sheet. Some of my subjects took an additional two tests, and Scores 3 & 4 are in another sheet. How can I merge the two sheets and align the rows so that each subject has all four scores in individual cells in a single row?
The first sheet looks like this: all subjects have a unique 4-digit ID and numeric Scores 1 & 2.
1 4056 4 6
2 4089 6 8
3 4192 7 8
4 4193 2 5
5 4202 3 5
The second sheet looks like this. The subjects are the same people, but only some of the people from the first sheet completed Scores 3 & 4.
1 4056 8 9
2 4192 2 4
3 4202 6 8
What I want is a single sheet in which the rows are aligned by subject ID, and all the scores are in their own cells.
1 4056 4 6 8 9
2 4089 6 8
3 4192 7 8 2 4
4 4193 2 5
5 4202 3 5 6 8
I thought this would be easier but I'm stumped. Any ideas?
The first sheet looks like this: all subjects have a unique 4-digit ID and numeric Scores 1 & 2.
1 4056 4 6
2 4089 6 8
3 4192 7 8
4 4193 2 5
5 4202 3 5
The second sheet looks like this. The subjects are the same people, but only some of the people from the first sheet completed Scores 3 & 4.
1 4056 8 9
2 4192 2 4
3 4202 6 8
What I want is a single sheet in which the rows are aligned by subject ID, and all the scores are in their own cells.
1 4056 4 6 8 9
2 4089 6 8
3 4192 7 8 2 4
4 4193 2 5
5 4202 3 5 6 8
I thought this would be easier but I'm stumped. Any ideas?
Best answer: (gaaah, sorry, don't fill across. In E1 put
=VLOOKUP($A1,AA:AC,3,0)
..and then fill down)
posted by pompomtom at 1:51 PM on March 24, 2011 [1 favorite]
=VLOOKUP($A1,AA:AC,3,0)
..and then fill down)
posted by pompomtom at 1:51 PM on March 24, 2011 [1 favorite]
Response by poster: Fantastic. Thank you. I knew there must be a straightforward answer!
posted by oceanmorning at 2:02 PM on March 24, 2011
posted by oceanmorning at 2:02 PM on March 24, 2011
pompomtom has it. You are looking for vlookup.
It is essentially filling the 3rd and 4th test cells with info from that set of data where the ID's match.
posted by milqman at 2:03 PM on March 24, 2011
It is essentially filling the 3rd and 4th test cells with info from that set of data where the ID's match.
posted by milqman at 2:03 PM on March 24, 2011
Best answer: If you would like to avoid seeing #N/A where subjects did not take test 3 or 4, you can use the same VLOOKUP, and display a blank cell if no results are found.
=IF(ISNA(VLOOKUP($A1,AA:AC,2,0)),"",VLOOKUP($A1,AA:AC,2,0))
The ISNA function evaluates the VLOOKUP result. If it is "#N/A", indicating that the Lookup_Value was not found in the Table_array, the cell shows a null. If false, the VLOOKUP result is shown.
posted by Snerd at 5:36 PM on March 24, 2011 [1 favorite]
=IF(ISNA(VLOOKUP($A1,AA:AC,2,0)),"",VLOOKUP($A1,AA:AC,2,0))
The ISNA function evaluates the VLOOKUP result. If it is "#N/A", indicating that the Lookup_Value was not found in the Table_array, the cell shows a null. If false, the VLOOKUP result is shown.
posted by Snerd at 5:36 PM on March 24, 2011 [1 favorite]
Response by poster: Wow, I have learned two tricks here that will save me HOURS of fiddly formatting work in the future! Thanks!
posted by oceanmorning at 6:15 PM on March 24, 2011
posted by oceanmorning at 6:15 PM on March 24, 2011
This thread is closed to new comments.
Now, in cell D1 enter:
=VLOOKUP($A1,AA:AC,2,0)
...and now fill across and down.
posted by pompomtom at 1:50 PM on March 24, 2011 [1 favorite]