How to merge Excel worksheets by stacking columns, using an ID column?
I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ 173
GHI 999
I'd like to merge them and have:
ID VALUE1 VALUE2
---------------------
ABC 8274 11.13
DEF 613
GHI 236 999
XYZ 173
How to do such a merging with Excel 2007?
microsoft-excel worksheet-function microsoft-excel-2007 spreadsheet
asked Jan 10 at 8:29
BasjBasj
765628
add a comment |
I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ 173
GHI 999
I'd like to merge them and have:
ID VALUE1 VALUE2
---------------------
ABC 8274 11.13
DEF 613
GHI 236 999
XYZ 173
How to do such a merging with Excel 2007?
microsoft-excel worksheet-function microsoft-excel-2007 spreadsheet
asked Jan 10 at 8:29
BasjBasj
765628
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35
add a comment |
I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ 173
GHI 999
I'd like to merge them and have:
ID VALUE1 VALUE2
---------------------
ABC 8274 11.13
DEF 613
GHI 236 999
XYZ 173
How to do such a merging with Excel 2007?
microsoft-excel worksheet-function microsoft-excel-2007 spreadsheet
asked Jan 10 at 8:29
BasjBasj
765628
I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ 173
GHI 999
I'd like to merge them and have:
ID VALUE1 VALUE2
---------------------
ABC 8274 11.13
DEF 613
GHI 236 999
XYZ 173
How to do such a merging with Excel 2007?
microsoft-excel worksheet-function microsoft-excel-2007 spreadsheet
microsoft-excel worksheet-function microsoft-excel-2007 spreadsheet
asked Jan 10 at 8:29
BasjBasj
765628
asked Jan 10 at 8:29
BasjBasj
765628
asked Jan 10 at 8:29
BasjBasj
765628
asked Jan 10 at 8:29
BasjBasj
765628
asked Jan 10 at 8:29
BasjBasj
765628
765628
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35
add a comment |
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35
add a comment |
2 Answers
2
active
oldest
votes
TLDR : filter unique ID, then use index match + iferror() .
Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :
Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"")in B2 &=IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"")in C2drag both cell downwards.
That should work. Have a try. ( :
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
add a comment |
I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:
Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.
answered Jan 10 at 10:50
BasjBasj
765628
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f1392648%2fhow-to-merge-excel-worksheets-by-stacking-columns-using-an-id-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
TLDR : filter unique ID, then use index match + iferror() .
Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :
Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"")in B2 &=IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"")in C2drag both cell downwards.
That should work. Have a try. ( :
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
add a comment |
TLDR : filter unique ID, then use index match + iferror() .
Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :
Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"")in B2 &=IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"")in C2drag both cell downwards.
That should work. Have a try. ( :
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
add a comment |
TLDR : filter unique ID, then use index match + iferror() .
Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :
Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"")in B2 &=IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"")in C2drag both cell downwards.
That should work. Have a try. ( :
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
TLDR : filter unique ID, then use index match + iferror() .
Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :
Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"")in B2 &=IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"")in C2drag both cell downwards.
That should work. Have a try. ( :
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
answered Jan 10 at 10:00
p._phidot_p._phidot_
586210
586210
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
add a comment |
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
Thank you for your answer @p._phidot_. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"?
– Basj
Jan 10 at 10:13
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :
– p._phidot_
Jan 10 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Jan 10 at 10:51
1
1
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^)
– p._phidot_
Jan 10 at 10:57
add a comment |
I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:
Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.
answered Jan 10 at 10:50
BasjBasj
765628
add a comment |
I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:
Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.
answered Jan 10 at 10:50
BasjBasj
765628
add a comment |
I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:
Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.
answered Jan 10 at 10:50
BasjBasj
765628
I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:
Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.
answered Jan 10 at 10:50
BasjBasj
765628
answered Jan 10 at 10:50
BasjBasj
765628
answered Jan 10 at 10:50
BasjBasj
765628
answered Jan 10 at 10:50
BasjBasj
765628
765628
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f1392648%2fhow-to-merge-excel-worksheets-by-stacking-columns-using-an-id-column%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
You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/…
– Lee
Jan 11 at 1:35