Comparing 2 different datasets in Excel, trying to find the same entries in either set
I have 2 excel datasets, I am trying to compare data between the 2 sets and see if the same entry shows up in both. I have 39000 entries in one and 2000 in the other, is there a quick way around it?
microsoft-excel database
asked Feb 6 at 17:45
ChiChi
6
add a comment |
I have 2 excel datasets, I am trying to compare data between the 2 sets and see if the same entry shows up in both. I have 39000 entries in one and 2000 in the other, is there a quick way around it?
microsoft-excel database
asked Feb 6 at 17:45
ChiChi
6
2
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
COUNTIF()/COUNTIFS()?
– BruceWayne
Feb 6 at 17:49
add a comment |
I have 2 excel datasets, I am trying to compare data between the 2 sets and see if the same entry shows up in both. I have 39000 entries in one and 2000 in the other, is there a quick way around it?
microsoft-excel database
asked Feb 6 at 17:45
ChiChi
6
I have 2 excel datasets, I am trying to compare data between the 2 sets and see if the same entry shows up in both. I have 39000 entries in one and 2000 in the other, is there a quick way around it?
microsoft-excel database
microsoft-excel database
asked Feb 6 at 17:45
ChiChi
6
asked Feb 6 at 17:45
ChiChi
6
asked Feb 6 at 17:45
ChiChi
6
asked Feb 6 at 17:45
ChiChi
6
asked Feb 6 at 17:45
ChiChi
6
6
2
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
COUNTIF()/COUNTIFS()?
– BruceWayne
Feb 6 at 17:49
add a comment |
2
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
COUNTIF()/COUNTIFS()?
– BruceWayne
Feb 6 at 17:49
2
2
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
COUNTIF()/COUNTIFS()?– BruceWayne
Feb 6 at 17:49
COUNTIF()/COUNTIFS()?– BruceWayne
Feb 6 at 17:49
add a comment |
1 Answer
1
active
oldest
votes
Make a third excel sheet,
Put all rows of "field to be matched" from Sheet 1 as well as from Sheet 2 into this Sheet 3.
Sort this column in Sheet 3.
Go to menu - Data - click on Remove Duplicates (in data tools), this will remove all duplicates and you will have a unique value of each data in Sheet 3
in Sheet 3 - B1, Enter =COUNTIF(Sheet1!A:A,$A1)
in Sheet 3 - C1, Enter =COUNTIF(Sheet2!A:A,$A1)
copy these formula in B1 and C1 to down the entire length of your values in A.
In B column, you have the count how many time A1 value appears in Sheet1
In C column, you have the count how many time A1 value appears in Sheet2
answered Feb 6 at 18:23
VSRawatVSRawat
15512
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
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%2f1402785%2fcomparing-2-different-datasets-in-excel-trying-to-find-the-same-entries-in-eith%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
Make a third excel sheet,
Put all rows of "field to be matched" from Sheet 1 as well as from Sheet 2 into this Sheet 3.
Sort this column in Sheet 3.
Go to menu - Data - click on Remove Duplicates (in data tools), this will remove all duplicates and you will have a unique value of each data in Sheet 3
in Sheet 3 - B1, Enter =COUNTIF(Sheet1!A:A,$A1)
in Sheet 3 - C1, Enter =COUNTIF(Sheet2!A:A,$A1)
copy these formula in B1 and C1 to down the entire length of your values in A.
In B column, you have the count how many time A1 value appears in Sheet1
In C column, you have the count how many time A1 value appears in Sheet2
answered Feb 6 at 18:23
VSRawatVSRawat
15512
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
add a comment |
Make a third excel sheet,
Put all rows of "field to be matched" from Sheet 1 as well as from Sheet 2 into this Sheet 3.
Sort this column in Sheet 3.
Go to menu - Data - click on Remove Duplicates (in data tools), this will remove all duplicates and you will have a unique value of each data in Sheet 3
in Sheet 3 - B1, Enter =COUNTIF(Sheet1!A:A,$A1)
in Sheet 3 - C1, Enter =COUNTIF(Sheet2!A:A,$A1)
copy these formula in B1 and C1 to down the entire length of your values in A.
In B column, you have the count how many time A1 value appears in Sheet1
In C column, you have the count how many time A1 value appears in Sheet2
answered Feb 6 at 18:23
VSRawatVSRawat
15512
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
add a comment |
Make a third excel sheet,
Put all rows of "field to be matched" from Sheet 1 as well as from Sheet 2 into this Sheet 3.
Sort this column in Sheet 3.
Go to menu - Data - click on Remove Duplicates (in data tools), this will remove all duplicates and you will have a unique value of each data in Sheet 3
in Sheet 3 - B1, Enter =COUNTIF(Sheet1!A:A,$A1)
in Sheet 3 - C1, Enter =COUNTIF(Sheet2!A:A,$A1)
copy these formula in B1 and C1 to down the entire length of your values in A.
In B column, you have the count how many time A1 value appears in Sheet1
In C column, you have the count how many time A1 value appears in Sheet2
answered Feb 6 at 18:23
VSRawatVSRawat
15512
Make a third excel sheet,
Put all rows of "field to be matched" from Sheet 1 as well as from Sheet 2 into this Sheet 3.
Sort this column in Sheet 3.
Go to menu - Data - click on Remove Duplicates (in data tools), this will remove all duplicates and you will have a unique value of each data in Sheet 3
in Sheet 3 - B1, Enter =COUNTIF(Sheet1!A:A,$A1)
in Sheet 3 - C1, Enter =COUNTIF(Sheet2!A:A,$A1)
copy these formula in B1 and C1 to down the entire length of your values in A.
In B column, you have the count how many time A1 value appears in Sheet1
In C column, you have the count how many time A1 value appears in Sheet2
answered Feb 6 at 18:23
VSRawatVSRawat
15512
answered Feb 6 at 18:23
VSRawatVSRawat
15512
answered Feb 6 at 18:23
VSRawatVSRawat
15512
answered Feb 6 at 18:23
VSRawatVSRawat
15512
15512
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
add a comment |
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
1
1
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
Will this tell me where the duplicates are?
– Chi
Feb 6 at 20:12
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
No. That was not asked in the question.
– VSRawat
Feb 6 at 20:23
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
That is why you were advised above by cybernatic to elaborate your question to tell what all output you want.
– VSRawat
Feb 6 at 20:24
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
If duplicates are to be identified, just sort each individual table and put conditional formatting of showing duplicate which a direct menu command in excel. That will show duplicates next to next in a color which is different from non-duplicates' color so easily identifiable.
– VSRawat
Feb 6 at 20:25
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%2f1402785%2fcomparing-2-different-datasets-in-excel-trying-to-find-the-same-entries-in-eith%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
2
Can you please edit your question to provide sample data, expected output and what you've tried so far? As it is, we don't even know if your entries are in one cell or multiple cells...
– cybernetic.nomad
Feb 6 at 17:48
COUNTIF()/COUNTIFS()?– BruceWayne
Feb 6 at 17:49