Merging data in Excel, but keep duplicates
up vote
0
down vote
favorite
I have an Excel sheet with student names and ID numbers, and another sheet with ID numbers and testing information. The sheet with testing information has a row of English and a row of Math for each ID number, so the ID numbers are duplicated. I need to create a sheet with the Student names, ID numbers, and testing information with 2 rows for each student.
The sheet with testing information has more students than I need - I have the set of students in the first sheet with the names and ID numbers, so I can't just copy and paste. The current process is to just go through the larger set and delete the rows of students we don't need (for ~400/500 students, no thanks).
I was trying to follow this video - https://youtu.be/0uWjXuhvqB8 but I ended up with the same student IDs and test information for every student.
ETA Images:
Sheet1 - Student names & ID Numbers
Sheet2 - ID Numbers & Testing info
Sheet3 - Final format needed
microsoft-excel microsoft-excel-2016
asked Nov 19 at 17:51
JFeinAu
12
add a comment |
up vote
0
down vote
favorite
I have an Excel sheet with student names and ID numbers, and another sheet with ID numbers and testing information. The sheet with testing information has a row of English and a row of Math for each ID number, so the ID numbers are duplicated. I need to create a sheet with the Student names, ID numbers, and testing information with 2 rows for each student.
The sheet with testing information has more students than I need - I have the set of students in the first sheet with the names and ID numbers, so I can't just copy and paste. The current process is to just go through the larger set and delete the rows of students we don't need (for ~400/500 students, no thanks).
I was trying to follow this video - https://youtu.be/0uWjXuhvqB8 but I ended up with the same student IDs and test information for every student.
ETA Images:
Sheet1 - Student names & ID Numbers
Sheet2 - ID Numbers & Testing info
Sheet3 - Final format needed
microsoft-excel microsoft-excel-2016
asked Nov 19 at 17:51
JFeinAu
12
1
Sounds like you may be looking forVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 18:00
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have an Excel sheet with student names and ID numbers, and another sheet with ID numbers and testing information. The sheet with testing information has a row of English and a row of Math for each ID number, so the ID numbers are duplicated. I need to create a sheet with the Student names, ID numbers, and testing information with 2 rows for each student.
The sheet with testing information has more students than I need - I have the set of students in the first sheet with the names and ID numbers, so I can't just copy and paste. The current process is to just go through the larger set and delete the rows of students we don't need (for ~400/500 students, no thanks).
I was trying to follow this video - https://youtu.be/0uWjXuhvqB8 but I ended up with the same student IDs and test information for every student.
ETA Images:
Sheet1 - Student names & ID Numbers
Sheet2 - ID Numbers & Testing info
Sheet3 - Final format needed
microsoft-excel microsoft-excel-2016
asked Nov 19 at 17:51
JFeinAu
12
I have an Excel sheet with student names and ID numbers, and another sheet with ID numbers and testing information. The sheet with testing information has a row of English and a row of Math for each ID number, so the ID numbers are duplicated. I need to create a sheet with the Student names, ID numbers, and testing information with 2 rows for each student.
The sheet with testing information has more students than I need - I have the set of students in the first sheet with the names and ID numbers, so I can't just copy and paste. The current process is to just go through the larger set and delete the rows of students we don't need (for ~400/500 students, no thanks).
I was trying to follow this video - https://youtu.be/0uWjXuhvqB8 but I ended up with the same student IDs and test information for every student.
ETA Images:
Sheet1 - Student names & ID Numbers
Sheet2 - ID Numbers & Testing info
Sheet3 - Final format needed
microsoft-excel microsoft-excel-2016
microsoft-excel microsoft-excel-2016
asked Nov 19 at 17:51
JFeinAu
12
asked Nov 19 at 17:51
JFeinAu
12
edited Nov 20 at 2:27
asked Nov 19 at 17:51
JFeinAu
12
asked Nov 19 at 17:51
JFeinAu
12
asked Nov 19 at 17:51
JFeinAu
12
12
1
Sounds like you may be looking forVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 18:00
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39
add a comment |
1
Sounds like you may be looking forVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 18:00
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39
1
1
Sounds like you may be looking for
VLOOKUP and/or INDEX/MATCH– cybernetic.nomad
Nov 19 at 18:00
Sounds like you may be looking for
VLOOKUP and/or INDEX/MATCH– cybernetic.nomad
Nov 19 at 18:00
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376751%2fmerging-data-in-excel-but-keep-duplicates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Sounds like you may be looking for
VLOOKUPand/orINDEX/MATCH– cybernetic.nomad
Nov 19 at 18:00
Sheet 1: ibb.co/fq2QqL Sheet 2: ibb.co/eRNaqL Result Sheet: ibb.co/cRNVPf Result Sheet Column A formula: =Sheet1!A2 Column B formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE) Column C: =VLOOKUP(B2,Sheet2!A:B,2,FALSE)
– Lee
Nov 20 at 1:39