Compare two sheets in Excel and extract the differences to other third sheet
My situation:
I got a huge excel sheet from a manufacturer of products for my online shop. This format:
product ID | price | name | ...
Every week the manufacturer is sending me a updated list with new products. So I need to know which products are new in that list. So what I need is a code snippet, which does the following:
- I´m gonna insert the old product list in sheet1 (manually)
- I´m gonna insert the new product list in sheet2 (manually)
- Compare the product IDs of Sheet1 and Sheet2
- Insert the products (rows) from the new list (Sheet2) in Sheet3, which are not present in the old list (Sheet1)
So the result in Sheet3 would be all new products.
I hope you can push me in the right direction.
Regards
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
asked May 15 '15 at 13:55
m1crdym1crdy
10113
add a comment |
My situation:
I got a huge excel sheet from a manufacturer of products for my online shop. This format:
product ID | price | name | ...
Every week the manufacturer is sending me a updated list with new products. So I need to know which products are new in that list. So what I need is a code snippet, which does the following:
- I´m gonna insert the old product list in sheet1 (manually)
- I´m gonna insert the new product list in sheet2 (manually)
- Compare the product IDs of Sheet1 and Sheet2
- Insert the products (rows) from the new list (Sheet2) in Sheet3, which are not present in the old list (Sheet1)
So the result in Sheet3 would be all new products.
I hope you can push me in the right direction.
Regards
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
asked May 15 '15 at 13:55
m1crdym1crdy
10113
add a comment |
My situation:
I got a huge excel sheet from a manufacturer of products for my online shop. This format:
product ID | price | name | ...
Every week the manufacturer is sending me a updated list with new products. So I need to know which products are new in that list. So what I need is a code snippet, which does the following:
- I´m gonna insert the old product list in sheet1 (manually)
- I´m gonna insert the new product list in sheet2 (manually)
- Compare the product IDs of Sheet1 and Sheet2
- Insert the products (rows) from the new list (Sheet2) in Sheet3, which are not present in the old list (Sheet1)
So the result in Sheet3 would be all new products.
I hope you can push me in the right direction.
Regards
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
asked May 15 '15 at 13:55
m1crdym1crdy
10113
My situation:
I got a huge excel sheet from a manufacturer of products for my online shop. This format:
product ID | price | name | ...
Every week the manufacturer is sending me a updated list with new products. So I need to know which products are new in that list. So what I need is a code snippet, which does the following:
- I´m gonna insert the old product list in sheet1 (manually)
- I´m gonna insert the new product list in sheet2 (manually)
- Compare the product IDs of Sheet1 and Sheet2
- Insert the products (rows) from the new list (Sheet2) in Sheet3, which are not present in the old list (Sheet1)
So the result in Sheet3 would be all new products.
I hope you can push me in the right direction.
Regards
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
asked May 15 '15 at 13:55
m1crdym1crdy
10113
asked May 15 '15 at 13:55
m1crdym1crdy
10113
asked May 15 '15 at 13:55
m1crdym1crdy
10113
asked May 15 '15 at 13:55
m1crdym1crdy
10113
asked May 15 '15 at 13:55
m1crdym1crdy
10113
10113
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
I think You might need something like this:=VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE)
source: https://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell
Of course You can also solve this, using VBA with buttons and all kind of fancy things.
edited May 23 '17 at 12:41
Community♦
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
add a comment |
Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list.
The other way to do it is to keep the sheets in different files and use the Excel query tool to do a join query on the two tables into a new table.
Either way, once you have your new table, you save it out in readiness for the next merge.
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
add a comment |
In Sheet2 (updated item), add a column D that checks if the product is new:
=MATCH(A2,Sheet1!$A:$A,0)>0
Add a column E that gives the TRUE values in column D a unique identifier
=D5&COUNTIF(D$2:D2)
Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ever need (e.g. if one day there are 20 new items and you only went up to TRUE15, then you're going to miss 5 items).
You can then bring in the new items with:
=INDEX(Sheet2!A:A,MATCH($E2,Sheet2!$E:$E,0))
answered May 15 '15 at 18:34
selwythselwyth
1262
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%2f915232%2fcompare-two-sheets-in-excel-and-extract-the-differences-to-other-third-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think You might need something like this:=VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE)
source: https://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell
Of course You can also solve this, using VBA with buttons and all kind of fancy things.
edited May 23 '17 at 12:41
Community♦
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
add a comment |
I think You might need something like this:=VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE)
source: https://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell
Of course You can also solve this, using VBA with buttons and all kind of fancy things.
edited May 23 '17 at 12:41
Community♦
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
add a comment |
I think You might need something like this:=VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE)
source: https://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell
Of course You can also solve this, using VBA with buttons and all kind of fancy things.
edited May 23 '17 at 12:41
Community♦
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
I think You might need something like this:=VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE)
source: https://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell
Of course You can also solve this, using VBA with buttons and all kind of fancy things.
edited May 23 '17 at 12:41
Community♦
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
answered May 15 '15 at 14:04
Divin3Divin3
1,4281726
1,4281726
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
add a comment |
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
Did You manage to solve the problem? If yes, remember to accept the best answer as a solution. If You have more questions, feel confident to ask.
– Divin3
Jul 2 '15 at 8:24
add a comment |
Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list.
The other way to do it is to keep the sheets in different files and use the Excel query tool to do a join query on the two tables into a new table.
Either way, once you have your new table, you save it out in readiness for the next merge.
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
add a comment |
Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list.
The other way to do it is to keep the sheets in different files and use the Excel query tool to do a join query on the two tables into a new table.
Either way, once you have your new table, you save it out in readiness for the next merge.
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
add a comment |
Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list.
The other way to do it is to keep the sheets in different files and use the Excel query tool to do a join query on the two tables into a new table.
Either way, once you have your new table, you save it out in readiness for the next merge.
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list.
The other way to do it is to keep the sheets in different files and use the Excel query tool to do a join query on the two tables into a new table.
Either way, once you have your new table, you save it out in readiness for the next merge.
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
answered May 15 '15 at 17:48
Julian KnightJulian Knight
12.9k11535
12.9k11535
add a comment |
add a comment |
In Sheet2 (updated item), add a column D that checks if the product is new:
=MATCH(A2,Sheet1!$A:$A,0)>0
Add a column E that gives the TRUE values in column D a unique identifier
=D5&COUNTIF(D$2:D2)
Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ever need (e.g. if one day there are 20 new items and you only went up to TRUE15, then you're going to miss 5 items).
You can then bring in the new items with:
=INDEX(Sheet2!A:A,MATCH($E2,Sheet2!$E:$E,0))
answered May 15 '15 at 18:34
selwythselwyth
1262
add a comment |
In Sheet2 (updated item), add a column D that checks if the product is new:
=MATCH(A2,Sheet1!$A:$A,0)>0
Add a column E that gives the TRUE values in column D a unique identifier
=D5&COUNTIF(D$2:D2)
Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ever need (e.g. if one day there are 20 new items and you only went up to TRUE15, then you're going to miss 5 items).
You can then bring in the new items with:
=INDEX(Sheet2!A:A,MATCH($E2,Sheet2!$E:$E,0))
answered May 15 '15 at 18:34
selwythselwyth
1262
add a comment |
In Sheet2 (updated item), add a column D that checks if the product is new:
=MATCH(A2,Sheet1!$A:$A,0)>0
Add a column E that gives the TRUE values in column D a unique identifier
=D5&COUNTIF(D$2:D2)
Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ever need (e.g. if one day there are 20 new items and you only went up to TRUE15, then you're going to miss 5 items).
You can then bring in the new items with:
=INDEX(Sheet2!A:A,MATCH($E2,Sheet2!$E:$E,0))
answered May 15 '15 at 18:34
selwythselwyth
1262
In Sheet2 (updated item), add a column D that checks if the product is new:
=MATCH(A2,Sheet1!$A:$A,0)>0
Add a column E that gives the TRUE values in column D a unique identifier
=D5&COUNTIF(D$2:D2)
Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ever need (e.g. if one day there are 20 new items and you only went up to TRUE15, then you're going to miss 5 items).
You can then bring in the new items with:
=INDEX(Sheet2!A:A,MATCH($E2,Sheet2!$E:$E,0))
answered May 15 '15 at 18:34
selwythselwyth
1262
answered May 15 '15 at 18:34
selwythselwyth
1262
answered May 15 '15 at 18:34
selwythselwyth
1262
answered May 15 '15 at 18:34
selwythselwyth
1262
1262
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%2f915232%2fcompare-two-sheets-in-excel-and-extract-the-differences-to-other-third-sheet%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
