Finding the difference in quantity of matching items from two sheets
I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
The items are unsorted in both the sheets, plus they are not in the same order.
microsoft-excel worksheet-function microsoft-excel-2016
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
add a comment |
I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
The items are unsorted in both the sheets, plus they are not in the same order.
microsoft-excel worksheet-function microsoft-excel-2016
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
add a comment |
I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
The items are unsorted in both the sheets, plus they are not in the same order.
microsoft-excel worksheet-function microsoft-excel-2016
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
The items are unsorted in both the sheets, plus they are not in the same order.
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
edited Dec 10 '18 at 7:40
robinCTS
4,00841527
4,00841527
asked Dec 10 '18 at 6:24
Zorro
176
asked Dec 10 '18 at 6:24
Zorro
176
asked Dec 10 '18 at 6:24
Zorro
176
176
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
What you are after is the VLOOKUP() function.
Set up Sheet2 like this
and Sheet1 like this
Enter the following formula in C2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=ABS(B2-VLOOKUP(A2,Sheet2!$A$1:$B$8,2,FALSE))
Explanation:
The VLOOKUP() function works by looking up the value of the first argument in the first column of the range specified in the second argument, and returns the value from the same row in the column specified by the third argument. (The third argument is a one-based column index of the columns of the range of the second argument.)
The ABS() function is just there to make all the differences positive. It can be omitted if you also need to know if the quantity in Sheet2 in more or less than that in Sheet1.
The #N/A error results when the item in Sheet1 doesn't have a corresponding entry in Sheet2. If required, this can be changed to any string by using the IFERROR() function.
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
add a comment |
How it works:
Write this in Cell
G3ofSheet 2& fill it down.
=IFERROR(VLOOKUP(Sheet1!A3,$A$3:$B$9,2,False)-Sheet1!B3,"Not Found")
VLOOKUPfunction finds & match the Items between Sheets and Subtracts Quantity if found, otherwiseIFERRORreturns theNot Foundmessage.
Or you may use this one also in Cell H3of Sheet 2 & fill it down.
=IFERROR(ABS(IF(COUNTIF(A$3:A$9,Sheet1!A3)<>0,Sheet1!B3-INDEX(B$3:B$9,MATCH(Sheet1!A3,A$3:A$9,0))," ")),"Not Found")
- In first part of the formula
COUNTIFfind the match betweenItemsin both Sheets and if returns1, then the next part of the FormulaSubtracts Quantitiesbetween Sheets which is supported byINDEX & MATCHcombination.
Adjust Cell references in the Formula as needed.
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
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%2f1382238%2ffinding-the-difference-in-quantity-of-matching-items-from-two-sheets%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
What you are after is the VLOOKUP() function.
Set up Sheet2 like this
and Sheet1 like this
Enter the following formula in C2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=ABS(B2-VLOOKUP(A2,Sheet2!$A$1:$B$8,2,FALSE))
Explanation:
The VLOOKUP() function works by looking up the value of the first argument in the first column of the range specified in the second argument, and returns the value from the same row in the column specified by the third argument. (The third argument is a one-based column index of the columns of the range of the second argument.)
The ABS() function is just there to make all the differences positive. It can be omitted if you also need to know if the quantity in Sheet2 in more or less than that in Sheet1.
The #N/A error results when the item in Sheet1 doesn't have a corresponding entry in Sheet2. If required, this can be changed to any string by using the IFERROR() function.
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
add a comment |
What you are after is the VLOOKUP() function.
Set up Sheet2 like this
and Sheet1 like this
Enter the following formula in C2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=ABS(B2-VLOOKUP(A2,Sheet2!$A$1:$B$8,2,FALSE))
Explanation:
The VLOOKUP() function works by looking up the value of the first argument in the first column of the range specified in the second argument, and returns the value from the same row in the column specified by the third argument. (The third argument is a one-based column index of the columns of the range of the second argument.)
The ABS() function is just there to make all the differences positive. It can be omitted if you also need to know if the quantity in Sheet2 in more or less than that in Sheet1.
The #N/A error results when the item in Sheet1 doesn't have a corresponding entry in Sheet2. If required, this can be changed to any string by using the IFERROR() function.
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
add a comment |
What you are after is the VLOOKUP() function.
Set up Sheet2 like this
and Sheet1 like this
Enter the following formula in C2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=ABS(B2-VLOOKUP(A2,Sheet2!$A$1:$B$8,2,FALSE))
Explanation:
The VLOOKUP() function works by looking up the value of the first argument in the first column of the range specified in the second argument, and returns the value from the same row in the column specified by the third argument. (The third argument is a one-based column index of the columns of the range of the second argument.)
The ABS() function is just there to make all the differences positive. It can be omitted if you also need to know if the quantity in Sheet2 in more or less than that in Sheet1.
The #N/A error results when the item in Sheet1 doesn't have a corresponding entry in Sheet2. If required, this can be changed to any string by using the IFERROR() function.
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
What you are after is the VLOOKUP() function.
Set up Sheet2 like this
and Sheet1 like this
Enter the following formula in C2 and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=ABS(B2-VLOOKUP(A2,Sheet2!$A$1:$B$8,2,FALSE))
Explanation:
The VLOOKUP() function works by looking up the value of the first argument in the first column of the range specified in the second argument, and returns the value from the same row in the column specified by the third argument. (The third argument is a one-based column index of the columns of the range of the second argument.)
The ABS() function is just there to make all the differences positive. It can be omitted if you also need to know if the quantity in Sheet2 in more or less than that in Sheet1.
The #N/A error results when the item in Sheet1 doesn't have a corresponding entry in Sheet2. If required, this can be changed to any string by using the IFERROR() function.
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
edited Dec 10 '18 at 7:25
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
answered Dec 10 '18 at 7:12
robinCTS
4,00841527
4,00841527
add a comment |
add a comment |
How it works:
Write this in Cell
G3ofSheet 2& fill it down.
=IFERROR(VLOOKUP(Sheet1!A3,$A$3:$B$9,2,False)-Sheet1!B3,"Not Found")
VLOOKUPfunction finds & match the Items between Sheets and Subtracts Quantity if found, otherwiseIFERRORreturns theNot Foundmessage.
Or you may use this one also in Cell H3of Sheet 2 & fill it down.
=IFERROR(ABS(IF(COUNTIF(A$3:A$9,Sheet1!A3)<>0,Sheet1!B3-INDEX(B$3:B$9,MATCH(Sheet1!A3,A$3:A$9,0))," ")),"Not Found")
- In first part of the formula
COUNTIFfind the match betweenItemsin both Sheets and if returns1, then the next part of the FormulaSubtracts Quantitiesbetween Sheets which is supported byINDEX & MATCHcombination.
Adjust Cell references in the Formula as needed.
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
add a comment |
How it works:
Write this in Cell
G3ofSheet 2& fill it down.
=IFERROR(VLOOKUP(Sheet1!A3,$A$3:$B$9,2,False)-Sheet1!B3,"Not Found")
VLOOKUPfunction finds & match the Items between Sheets and Subtracts Quantity if found, otherwiseIFERRORreturns theNot Foundmessage.
Or you may use this one also in Cell H3of Sheet 2 & fill it down.
=IFERROR(ABS(IF(COUNTIF(A$3:A$9,Sheet1!A3)<>0,Sheet1!B3-INDEX(B$3:B$9,MATCH(Sheet1!A3,A$3:A$9,0))," ")),"Not Found")
- In first part of the formula
COUNTIFfind the match betweenItemsin both Sheets and if returns1, then the next part of the FormulaSubtracts Quantitiesbetween Sheets which is supported byINDEX & MATCHcombination.
Adjust Cell references in the Formula as needed.
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
add a comment |
How it works:
Write this in Cell
G3ofSheet 2& fill it down.
=IFERROR(VLOOKUP(Sheet1!A3,$A$3:$B$9,2,False)-Sheet1!B3,"Not Found")
VLOOKUPfunction finds & match the Items between Sheets and Subtracts Quantity if found, otherwiseIFERRORreturns theNot Foundmessage.
Or you may use this one also in Cell H3of Sheet 2 & fill it down.
=IFERROR(ABS(IF(COUNTIF(A$3:A$9,Sheet1!A3)<>0,Sheet1!B3-INDEX(B$3:B$9,MATCH(Sheet1!A3,A$3:A$9,0))," ")),"Not Found")
- In first part of the formula
COUNTIFfind the match betweenItemsin both Sheets and if returns1, then the next part of the FormulaSubtracts Quantitiesbetween Sheets which is supported byINDEX & MATCHcombination.
Adjust Cell references in the Formula as needed.
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
How it works:
Write this in Cell
G3ofSheet 2& fill it down.
=IFERROR(VLOOKUP(Sheet1!A3,$A$3:$B$9,2,False)-Sheet1!B3,"Not Found")
VLOOKUPfunction finds & match the Items between Sheets and Subtracts Quantity if found, otherwiseIFERRORreturns theNot Foundmessage.
Or you may use this one also in Cell H3of Sheet 2 & fill it down.
=IFERROR(ABS(IF(COUNTIF(A$3:A$9,Sheet1!A3)<>0,Sheet1!B3-INDEX(B$3:B$9,MATCH(Sheet1!A3,A$3:A$9,0))," ")),"Not Found")
- In first part of the formula
COUNTIFfind the match betweenItemsin both Sheets and if returns1, then the next part of the FormulaSubtracts Quantitiesbetween Sheets which is supported byINDEX & MATCHcombination.
Adjust Cell references in the Formula as needed.
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
edited Dec 10 '18 at 9:29
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
answered Dec 10 '18 at 7:25
Rajesh S
3,7021522
3,7021522
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
add a comment |
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
Write your concern why this post has been Down voted ???
– Rajesh S
Dec 11 '18 at 8:18
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.
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.
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%2f1382238%2ffinding-the-difference-in-quantity-of-matching-items-from-two-sheets%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
