How do I align two tables based on a unique column with Excel?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table.
I want to merge or align them so that all the data for each product is in one table.
E.g.
Table 1
ID Price Weight
1 £2 100g
2 £3 250g
3 £3.5 70g
4 £2.75 25g
5 £0.8 50g
.
.
.
Table 2
ID Colour Sold Stock ...
3 Red 98 102
4 Blue 50 50
.
.
.
I could use vlookup but that would only return one columns value, the second table has over 100 columns and I want to avoid writing that many! Any ideas appreciated.
microsoft-excel vlookup
edited Jul 7 '15 at 23:17
danicotra
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
add a comment |
I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table.
I want to merge or align them so that all the data for each product is in one table.
E.g.
Table 1
ID Price Weight
1 £2 100g
2 £3 250g
3 £3.5 70g
4 £2.75 25g
5 £0.8 50g
.
.
.
Table 2
ID Colour Sold Stock ...
3 Red 98 102
4 Blue 50 50
.
.
.
I could use vlookup but that would only return one columns value, the second table has over 100 columns and I want to avoid writing that many! Any ideas appreciated.
microsoft-excel vlookup
edited Jul 7 '15 at 23:17
danicotra
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
this should do what you want.
– duhamp
Jul 7 '15 at 22:14
add a comment |
I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table.
I want to merge or align them so that all the data for each product is in one table.
E.g.
Table 1
ID Price Weight
1 £2 100g
2 £3 250g
3 £3.5 70g
4 £2.75 25g
5 £0.8 50g
.
.
.
Table 2
ID Colour Sold Stock ...
3 Red 98 102
4 Blue 50 50
.
.
.
I could use vlookup but that would only return one columns value, the second table has over 100 columns and I want to avoid writing that many! Any ideas appreciated.
microsoft-excel vlookup
edited Jul 7 '15 at 23:17
danicotra
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table.
I want to merge or align them so that all the data for each product is in one table.
E.g.
Table 1
ID Price Weight
1 £2 100g
2 £3 250g
3 £3.5 70g
4 £2.75 25g
5 £0.8 50g
.
.
.
Table 2
ID Colour Sold Stock ...
3 Red 98 102
4 Blue 50 50
.
.
.
I could use vlookup but that would only return one columns value, the second table has over 100 columns and I want to avoid writing that many! Any ideas appreciated.
microsoft-excel vlookup
microsoft-excel vlookup
edited Jul 7 '15 at 23:17
danicotra
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
edited Jul 7 '15 at 23:17
danicotra
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
edited Jul 7 '15 at 23:17
danicotra
1,1092621
edited Jul 7 '15 at 23:17
danicotra
1,1092621
edited Jul 7 '15 at 23:17
danicotra
1,1092621
1,1092621
asked Jul 7 '15 at 20:04
BenBen
1112
asked Jul 7 '15 at 20:04
BenBen
1112
asked Jul 7 '15 at 20:04
BenBen
1112
1112
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
this should do what you want.
– duhamp
Jul 7 '15 at 22:14
add a comment |
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
this should do what you want.
– duhamp
Jul 7 '15 at 22:14
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
this should do what you want.
– duhamp
Jul 7 '15 at 22:14
this should do what you want.
– duhamp
Jul 7 '15 at 22:14
add a comment |
1 Answer
1
active
oldest
votes
I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:
Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:
=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")
notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism.
It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!
Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.
You could check also:
this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.
edited Mar 20 '17 at 10:04
Community♦
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
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%2f937546%2fhow-do-i-align-two-tables-based-on-a-unique-column-with-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:
Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:
=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")
notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism.
It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!
Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.
You could check also:
this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.
edited Mar 20 '17 at 10:04
Community♦
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
add a comment |
I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:
Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:
=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")
notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism.
It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!
Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.
You could check also:
this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.
edited Mar 20 '17 at 10:04
Community♦
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
add a comment |
I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:
Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:
=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")
notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism.
It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!
Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.
You could check also:
this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.
edited Mar 20 '17 at 10:04
Community♦
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
I think there would be nothing bad in using VLOOKUP for this; you could write a formula based upon the actual COLUMN() for the vlookup-index in a way like this for example:
Assuming the first table data starts from A1 cell and data are contained in columns A:E and the second table is contained on a different worksheet named "Table2" on cells from A1 to ZZ100, put the following formula on the first row and on the first free column:
=IFERROR(VLOOKUP(A1,Table2!$A$1:$ZZ$100,COLUMN()-4,FALSE),"")
notice I used "COLUMN()-4" as index for the VLOOKUP because last data column on the table is E while, if it was D, I would have used "COLUMN()-3" and if it was F "COLUMN()-5" instead... I hope you understand the operation mechanism.
It would be then very easy to simply paste the formula over the 100 (or even more) subsequent columns without the pain of having to change the vlookup-index in the formula for every column!
Otherwise you can check out even: DigDB or Merge Tables Wizard or Power-Query for this.
You could check also:
this other superuser question or here if you want to learn about data consolidation and, finally, this one if you want to solve with VBA.
edited Mar 20 '17 at 10:04
Community♦
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
edited Mar 20 '17 at 10:04
Community♦
1
edited Mar 20 '17 at 10:04
Community♦
1
edited Mar 20 '17 at 10:04
Community♦
1
1
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
answered Jul 7 '15 at 22:17
danicotradanicotra
1,1092621
1,1092621
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
add a comment |
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
Look at teylyn answer to this question on StackOverflow, it has the same solution approach and may help you better understand.
– danicotra
Jul 7 '15 at 22:38
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%2f937546%2fhow-do-i-align-two-tables-based-on-a-unique-column-with-excel%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
are you using excel 2013? if you are I suggest you look into using the relational database functions that have been added; its designed with problems such as this in mind
– duhamp
Jul 7 '15 at 22:13
this should do what you want.
– duhamp
Jul 7 '15 at 22:14