Merge ttwo tables using order id number where one table has multiple rows with the same id number
I'll first explain what I'm trying to accomplish then give the table details. I'm trying to export sales data from my website and organize it by order id number so I can easily see which orders were placed by wholesale customers and which weren't as well as which orders failed. The end goal is to be able to get a total quantity of an item sold and also see if it was wholesale or not.
The problem is that my site will only export the data in two separate tables.
Table 1 has the ORDER ID NUMBER and all the customer information including membership level and if an order is processed, complete, failed, etc.
Table 2 has the order information.
If more than one product was ordered, it will list the items in different rows
and each row has the (same) ORDER ID NUMBER in Column A.
Here is an example of the tables with the irrelevant columns removed...
Here is what I want the output to look like...
worksheet-function openoffice spreadsheet
edited Jan 24 at 0:51
Scott
15.9k113990
asked Jan 23 at 22:16
PetePete
1
add a comment |
I'll first explain what I'm trying to accomplish then give the table details. I'm trying to export sales data from my website and organize it by order id number so I can easily see which orders were placed by wholesale customers and which weren't as well as which orders failed. The end goal is to be able to get a total quantity of an item sold and also see if it was wholesale or not.
The problem is that my site will only export the data in two separate tables.
Table 1 has the ORDER ID NUMBER and all the customer information including membership level and if an order is processed, complete, failed, etc.
Table 2 has the order information.
If more than one product was ordered, it will list the items in different rows
and each row has the (same) ORDER ID NUMBER in Column A.
Here is an example of the tables with the irrelevant columns removed...
Here is what I want the output to look like...
worksheet-function openoffice spreadsheet
edited Jan 24 at 0:51
Scott
15.9k113990
asked Jan 23 at 22:16
PetePete
1
1
(1) This looks / sounds like a typical case for usingVLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.
– Scott
Jan 24 at 0:51
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00
add a comment |
I'll first explain what I'm trying to accomplish then give the table details. I'm trying to export sales data from my website and organize it by order id number so I can easily see which orders were placed by wholesale customers and which weren't as well as which orders failed. The end goal is to be able to get a total quantity of an item sold and also see if it was wholesale or not.
The problem is that my site will only export the data in two separate tables.
Table 1 has the ORDER ID NUMBER and all the customer information including membership level and if an order is processed, complete, failed, etc.
Table 2 has the order information.
If more than one product was ordered, it will list the items in different rows
and each row has the (same) ORDER ID NUMBER in Column A.
Here is an example of the tables with the irrelevant columns removed...
Here is what I want the output to look like...
worksheet-function openoffice spreadsheet
edited Jan 24 at 0:51
Scott
15.9k113990
asked Jan 23 at 22:16
PetePete
1
I'll first explain what I'm trying to accomplish then give the table details. I'm trying to export sales data from my website and organize it by order id number so I can easily see which orders were placed by wholesale customers and which weren't as well as which orders failed. The end goal is to be able to get a total quantity of an item sold and also see if it was wholesale or not.
The problem is that my site will only export the data in two separate tables.
Table 1 has the ORDER ID NUMBER and all the customer information including membership level and if an order is processed, complete, failed, etc.
Table 2 has the order information.
If more than one product was ordered, it will list the items in different rows
and each row has the (same) ORDER ID NUMBER in Column A.
Here is an example of the tables with the irrelevant columns removed...
Here is what I want the output to look like...
worksheet-function openoffice spreadsheet
worksheet-function openoffice spreadsheet
edited Jan 24 at 0:51
Scott
15.9k113990
asked Jan 23 at 22:16
PetePete
1
edited Jan 24 at 0:51
Scott
15.9k113990
asked Jan 23 at 22:16
PetePete
1
edited Jan 24 at 0:51
Scott
15.9k113990
edited Jan 24 at 0:51
Scott
15.9k113990
edited Jan 24 at 0:51
Scott
15.9k113990
15.9k113990
asked Jan 23 at 22:16
PetePete
1
asked Jan 23 at 22:16
PetePete
1
asked Jan 23 at 22:16
PetePete
1
1
1
(1) This looks / sounds like a typical case for usingVLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.
– Scott
Jan 24 at 0:51
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00
add a comment |
1
(1) This looks / sounds like a typical case for usingVLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.
– Scott
Jan 24 at 0:51
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00
1
1
(1) This looks / sounds like a typical case for using
VLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.– Scott
Jan 24 at 0:51
(1) This looks / sounds like a typical case for using
VLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.– Scott
Jan 24 at 0:51
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00
add a comment |
0
active
oldest
votes
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%2f1397667%2fmerge-ttwo-tables-using-order-id-number-where-one-table-has-multiple-rows-with-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f1397667%2fmerge-ttwo-tables-using-order-id-number-where-one-table-has-multiple-rows-with-t%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
(1) This looks / sounds like a typical case for using
VLOOKUP. What have you tried? (2) We prefer that example data be posted as text rather than images.– Scott
Jan 24 at 0:51
sorry for the image, it is my first time on here and when I tried pasting my samples it told me it had to change them from the spreadsheet to an image. I'll do it right next time. I've tried VLOOKUP, found some sample code on line =VLOOKUP(A1;Sheet2.$A$1:$B$3;2;0) I made 2 junk tables with numbers and letters just to see if I could get it to work but didn't have any luck. It sort of worked but only if the A1 numbers were in the exact same order in both tables. If I changed the order of the A1 numbers then it put the incorrect letters into the adjacent cell. I also tried CONSOLIDATE data.
– Pete
Jan 24 at 14:00