How to compare two table with duplicate key reference
up vote
0
down vote
favorite
I have two worksheets, and there are many columns and rows inside.
The same key reference is " order number" with two worksheets.
But unfortunately, the order numbers are duplicate.
is there any way I can compare the two sheets in one sheet and one time?
Sheet A
ordernumber cost QTY date ......
A10 50 2 12/05/2018
A18 20 2 12/05/2018
A10 50 3 12/01/2018
A18 20 10 12/02/2018
A13 50 7 12/04/2018
Sheet B
ordernumber cost QTY date ......
A10 50 3 12/01/2018
A13 50 7 12/04/2018
A18 20 5 12/05/2018
A18 20 2 12/02/2018
compare sheet
ordernumber QTYA QTY B GAPQTY
A10 5 3 2
A18 12 7 5
A13 7 7 0
microsoft-excel worksheet-function
edited Dec 4 at 9:56
fixer1234
17.7k144581
asked Dec 4 at 9:45
dora chen
1
add a comment |
up vote
0
down vote
favorite
I have two worksheets, and there are many columns and rows inside.
The same key reference is " order number" with two worksheets.
But unfortunately, the order numbers are duplicate.
is there any way I can compare the two sheets in one sheet and one time?
Sheet A
ordernumber cost QTY date ......
A10 50 2 12/05/2018
A18 20 2 12/05/2018
A10 50 3 12/01/2018
A18 20 10 12/02/2018
A13 50 7 12/04/2018
Sheet B
ordernumber cost QTY date ......
A10 50 3 12/01/2018
A13 50 7 12/04/2018
A18 20 5 12/05/2018
A18 20 2 12/02/2018
compare sheet
ordernumber QTYA QTY B GAPQTY
A10 5 3 2
A18 12 7 5
A13 7 7 0
microsoft-excel worksheet-function
edited Dec 4 at 9:56
fixer1234
17.7k144581
asked Dec 4 at 9:45
dora chen
1
1
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two worksheets, and there are many columns and rows inside.
The same key reference is " order number" with two worksheets.
But unfortunately, the order numbers are duplicate.
is there any way I can compare the two sheets in one sheet and one time?
Sheet A
ordernumber cost QTY date ......
A10 50 2 12/05/2018
A18 20 2 12/05/2018
A10 50 3 12/01/2018
A18 20 10 12/02/2018
A13 50 7 12/04/2018
Sheet B
ordernumber cost QTY date ......
A10 50 3 12/01/2018
A13 50 7 12/04/2018
A18 20 5 12/05/2018
A18 20 2 12/02/2018
compare sheet
ordernumber QTYA QTY B GAPQTY
A10 5 3 2
A18 12 7 5
A13 7 7 0
microsoft-excel worksheet-function
edited Dec 4 at 9:56
fixer1234
17.7k144581
asked Dec 4 at 9:45
dora chen
1
I have two worksheets, and there are many columns and rows inside.
The same key reference is " order number" with two worksheets.
But unfortunately, the order numbers are duplicate.
is there any way I can compare the two sheets in one sheet and one time?
Sheet A
ordernumber cost QTY date ......
A10 50 2 12/05/2018
A18 20 2 12/05/2018
A10 50 3 12/01/2018
A18 20 10 12/02/2018
A13 50 7 12/04/2018
Sheet B
ordernumber cost QTY date ......
A10 50 3 12/01/2018
A13 50 7 12/04/2018
A18 20 5 12/05/2018
A18 20 2 12/02/2018
compare sheet
ordernumber QTYA QTY B GAPQTY
A10 5 3 2
A18 12 7 5
A13 7 7 0
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Dec 4 at 9:56
fixer1234
17.7k144581
asked Dec 4 at 9:45
dora chen
1
edited Dec 4 at 9:56
fixer1234
17.7k144581
asked Dec 4 at 9:45
dora chen
1
edited Dec 4 at 9:56
fixer1234
17.7k144581
edited Dec 4 at 9:56
fixer1234
17.7k144581
edited Dec 4 at 9:56
fixer1234
17.7k144581
17.7k144581
asked Dec 4 at 9:45
dora chen
1
asked Dec 4 at 9:45
dora chen
1
asked Dec 4 at 9:45
dora chen
1
1
1
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37
add a comment |
1
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37
1
1
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
How it works:
Write this Array Formula in Cell
A2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}
Enter this Array Formula in Cell
B2of Compare Sheet, finish withCtrl+Shift+Enter& Fill down.
{=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
Write this Formula in Cell
C2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
Finally in Cell
D2of Compare Sheet write this Formula and fill down.
=B2-C2
answered Dec 4 at 10:41
Rajesh S
3,6271522
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match executeCountifto create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.
– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake withCountif data Range,. I guess that you are using this Formula in SheetM10. Write bothCountiflike thisCOUNTIF($A$1:A1, will solve the issue.
– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
|
show 3 more comments
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
});
}
});
draft saved
draft discarded
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%2f1380641%2fhow-to-compare-two-table-with-duplicate-key-reference%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
up vote
0
down vote
How it works:
Write this Array Formula in Cell
A2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}
Enter this Array Formula in Cell
B2of Compare Sheet, finish withCtrl+Shift+Enter& Fill down.
{=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
Write this Formula in Cell
C2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
Finally in Cell
D2of Compare Sheet write this Formula and fill down.
=B2-C2
answered Dec 4 at 10:41
Rajesh S
3,6271522
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match executeCountifto create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.
– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake withCountif data Range,. I guess that you are using this Formula in SheetM10. Write bothCountiflike thisCOUNTIF($A$1:A1, will solve the issue.
– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
|
show 3 more comments
up vote
0
down vote
How it works:
Write this Array Formula in Cell
A2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}
Enter this Array Formula in Cell
B2of Compare Sheet, finish withCtrl+Shift+Enter& Fill down.
{=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
Write this Formula in Cell
C2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
Finally in Cell
D2of Compare Sheet write this Formula and fill down.
=B2-C2
answered Dec 4 at 10:41
Rajesh S
3,6271522
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match executeCountifto create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.
– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake withCountif data Range,. I guess that you are using this Formula in SheetM10. Write bothCountiflike thisCOUNTIF($A$1:A1, will solve the issue.
– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
|
show 3 more comments
up vote
0
down vote
up vote
0
down vote
How it works:
Write this Array Formula in Cell
A2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}
Enter this Array Formula in Cell
B2of Compare Sheet, finish withCtrl+Shift+Enter& Fill down.
{=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
Write this Formula in Cell
C2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
Finally in Cell
D2of Compare Sheet write this Formula and fill down.
=B2-C2
answered Dec 4 at 10:41
Rajesh S
3,6271522
How it works:
Write this Array Formula in Cell
A2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}
Enter this Array Formula in Cell
B2of Compare Sheet, finish withCtrl+Shift+Enter& Fill down.
{=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
Write this Formula in Cell
C2of Compare Sheet, finish withCtrl+Shift+Enter& fill it down.
{=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
Finally in Cell
D2of Compare Sheet write this Formula and fill down.
=B2-C2
answered Dec 4 at 10:41
Rajesh S
3,6271522
answered Dec 4 at 10:41
Rajesh S
3,6271522
answered Dec 4 at 10:41
Rajesh S
3,6271522
answered Dec 4 at 10:41
Rajesh S
3,6271522
3,6271522
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match executeCountifto create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.
– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake withCountif data Range,. I guess that you are using this Formula in SheetM10. Write bothCountiflike thisCOUNTIF($A$1:A1, will solve the issue.
– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
|
show 3 more comments
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match executeCountifto create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.
– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake withCountif data Range,. I guess that you are using this Formula in SheetM10. Write bothCountiflike thisCOUNTIF($A$1:A1, will solve the issue.
– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
For 2.3.4 are ok, but 1. is not working well. each order duplicate two lines, why put 0 inside match here? and if I have more than 10000 rows, will it take lots of time? is there any way can use a pivot table or query?
– dora chen
Dec 5 at 5:14
@dorachen,, Zero with Match execute
Countif to create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.– Rajesh S
Dec 5 at 5:35
@dorachen,, Zero with Match execute
Countif to create Unique rows, and the screen shot is part of the active sheet I've worked with all working formulas before post here. Problem is at your end! There are several methods but the suggested one in better and Faster,, could works for tons of rows.– Rajesh S
Dec 5 at 5:35
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
I agree with you. Problem is in my side, I really don't know array but I try many times and it still not work. Below is my formula, sometimes three cells work but sometimes only two cells. no idea why? =IFERROR(IFERROR(INDEX('M10'!L:L,MATCH(0,COUNTIF($A$1:A6,'M10'!L:L),0)),INDEX('All order'!A:A,MATCH(0,COUNTIF($A$1:A6,'All order'!A:A),0))),"") BTW, it really takes me lots of time to fix and save.
– dora chen
Dec 6 at 2:50
@dorachen, the mistake I found is that you committed mistake with
Countif data Range,. I guess that you are using this Formula in Sheet M10. Write both Countif like this COUNTIF($A$1:A1, will solve the issue.– Rajesh S
Dec 6 at 4:26
@dorachen, the mistake I found is that you committed mistake with
Countif data Range,. I guess that you are using this Formula in Sheet M10. Write both Countif like this COUNTIF($A$1:A1, will solve the issue.– Rajesh S
Dec 6 at 4:26
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
Cont.. my suggestion is first try the exercise in smaller data range as I've shown then on the real Range.
– Rajesh S
Dec 6 at 4:28
|
show 3 more comments
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f1380641%2fhow-to-compare-two-table-with-duplicate-key-reference%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
the order numbers are duplicate Use SUMIF() instead of single value obtained with VLOOKUP()...
– Akina
Dec 4 at 10:37