Excel check if combination of data exists in a certain range
up vote
0
down vote
favorite
See question 1337631
Check if a specific range of values in one dataset exists in another dataset
This solution works great, but what if you want to check WITHIN the same range?
Want to use this for 2 purposes:
1) while inputting data to alert that a certain combination has already been entered (which might have a valid reason due to other data being different)
2) To mark all duplicate records
To use this formula for the first purpose (input) I check in the range above the current entry. That works great, but does not allow purpose 2...
Anybody a suggestion to get a combination of it working? Should work when concatinating all except current row... But that's going the be a complicated alteration of the formula?
microsoft-excel comparison findstr
asked Dec 5 at 16:00
Daniel H
1
add a comment |
up vote
0
down vote
favorite
See question 1337631
Check if a specific range of values in one dataset exists in another dataset
This solution works great, but what if you want to check WITHIN the same range?
Want to use this for 2 purposes:
1) while inputting data to alert that a certain combination has already been entered (which might have a valid reason due to other data being different)
2) To mark all duplicate records
To use this formula for the first purpose (input) I check in the range above the current entry. That works great, but does not allow purpose 2...
Anybody a suggestion to get a combination of it working? Should work when concatinating all except current row... But that's going the be a complicated alteration of the formula?
microsoft-excel comparison findstr
asked Dec 5 at 16:00
Daniel H
1
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
See question 1337631
Check if a specific range of values in one dataset exists in another dataset
This solution works great, but what if you want to check WITHIN the same range?
Want to use this for 2 purposes:
1) while inputting data to alert that a certain combination has already been entered (which might have a valid reason due to other data being different)
2) To mark all duplicate records
To use this formula for the first purpose (input) I check in the range above the current entry. That works great, but does not allow purpose 2...
Anybody a suggestion to get a combination of it working? Should work when concatinating all except current row... But that's going the be a complicated alteration of the formula?
microsoft-excel comparison findstr
asked Dec 5 at 16:00
Daniel H
1
See question 1337631
Check if a specific range of values in one dataset exists in another dataset
This solution works great, but what if you want to check WITHIN the same range?
Want to use this for 2 purposes:
1) while inputting data to alert that a certain combination has already been entered (which might have a valid reason due to other data being different)
2) To mark all duplicate records
To use this formula for the first purpose (input) I check in the range above the current entry. That works great, but does not allow purpose 2...
Anybody a suggestion to get a combination of it working? Should work when concatinating all except current row... But that's going the be a complicated alteration of the formula?
microsoft-excel comparison findstr
microsoft-excel comparison findstr
asked Dec 5 at 16:00
Daniel H
1
asked Dec 5 at 16:00
Daniel H
1
asked Dec 5 at 16:00
Daniel H
1
asked Dec 5 at 16:00
Daniel H
1
asked Dec 5 at 16:00
Daniel H
1
1
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41
add a comment |
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
Let us say that your sample data has 3 columns and is in cells C3:F7.
Now in F3 put the following formula and press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall now be enclosed in curly braces automatically to indicate that it's an array formula.
Now drag it down up to the intended rows. The Unique or Duplicate Status of the row within the same table shall be indicated accordingly.
=IF(SUM(IF(C3&D3&E3=$C$3:$C$7&$D$3:$D$7&$E$3:$E$7,1,0))>1,"Duplicate","Unique")
answered Dec 5 at 16:54
pat2015
3,2142722
add a comment |
up vote
0
down vote
great alternative formula, except it drives the CPU up to 90% and causes the sheet to become unusable if you expand the formula to the following "open end range" to make it applicable for an expanding list ...
=IF(ISTEXT(D7);IF(SUM(IF(B7&C7&D7=$B:$B&$C:$C&$D:$D;1;0))>1;"Duplicate";"Unique");"")
Any solution for that to make it available? I now resticted it to rows 8-1000 and this works, but of course need to follow up the growth of the sheet!
answered Dec 6 at 9:50
Daniel H
1
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',
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%2f1381052%2fexcel-check-if-combination-of-data-exists-in-a-certain-range%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
up vote
2
down vote
Let us say that your sample data has 3 columns and is in cells C3:F7.
Now in F3 put the following formula and press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall now be enclosed in curly braces automatically to indicate that it's an array formula.
Now drag it down up to the intended rows. The Unique or Duplicate Status of the row within the same table shall be indicated accordingly.
=IF(SUM(IF(C3&D3&E3=$C$3:$C$7&$D$3:$D$7&$E$3:$E$7,1,0))>1,"Duplicate","Unique")
answered Dec 5 at 16:54
pat2015
3,2142722
add a comment |
up vote
2
down vote
Let us say that your sample data has 3 columns and is in cells C3:F7.
Now in F3 put the following formula and press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall now be enclosed in curly braces automatically to indicate that it's an array formula.
Now drag it down up to the intended rows. The Unique or Duplicate Status of the row within the same table shall be indicated accordingly.
=IF(SUM(IF(C3&D3&E3=$C$3:$C$7&$D$3:$D$7&$E$3:$E$7,1,0))>1,"Duplicate","Unique")
answered Dec 5 at 16:54
pat2015
3,2142722
add a comment |
up vote
2
down vote
up vote
2
down vote
Let us say that your sample data has 3 columns and is in cells C3:F7.
Now in F3 put the following formula and press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall now be enclosed in curly braces automatically to indicate that it's an array formula.
Now drag it down up to the intended rows. The Unique or Duplicate Status of the row within the same table shall be indicated accordingly.
=IF(SUM(IF(C3&D3&E3=$C$3:$C$7&$D$3:$D$7&$E$3:$E$7,1,0))>1,"Duplicate","Unique")
answered Dec 5 at 16:54
pat2015
3,2142722
Let us say that your sample data has 3 columns and is in cells C3:F7.
Now in F3 put the following formula and press CTRL + SHIFT + ENTER from within the Formula Bar to create an Array Formula. The formula shall now be enclosed in curly braces automatically to indicate that it's an array formula.
Now drag it down up to the intended rows. The Unique or Duplicate Status of the row within the same table shall be indicated accordingly.
=IF(SUM(IF(C3&D3&E3=$C$3:$C$7&$D$3:$D$7&$E$3:$E$7,1,0))>1,"Duplicate","Unique")
answered Dec 5 at 16:54
pat2015
3,2142722
answered Dec 5 at 16:54
pat2015
3,2142722
answered Dec 5 at 16:54
pat2015
3,2142722
answered Dec 5 at 16:54
pat2015
3,2142722
3,2142722
add a comment |
add a comment |
up vote
0
down vote
great alternative formula, except it drives the CPU up to 90% and causes the sheet to become unusable if you expand the formula to the following "open end range" to make it applicable for an expanding list ...
=IF(ISTEXT(D7);IF(SUM(IF(B7&C7&D7=$B:$B&$C:$C&$D:$D;1;0))>1;"Duplicate";"Unique");"")
Any solution for that to make it available? I now resticted it to rows 8-1000 and this works, but of course need to follow up the growth of the sheet!
answered Dec 6 at 9:50
Daniel H
1
add a comment |
up vote
0
down vote
great alternative formula, except it drives the CPU up to 90% and causes the sheet to become unusable if you expand the formula to the following "open end range" to make it applicable for an expanding list ...
=IF(ISTEXT(D7);IF(SUM(IF(B7&C7&D7=$B:$B&$C:$C&$D:$D;1;0))>1;"Duplicate";"Unique");"")
Any solution for that to make it available? I now resticted it to rows 8-1000 and this works, but of course need to follow up the growth of the sheet!
answered Dec 6 at 9:50
Daniel H
1
add a comment |
up vote
0
down vote
up vote
0
down vote
great alternative formula, except it drives the CPU up to 90% and causes the sheet to become unusable if you expand the formula to the following "open end range" to make it applicable for an expanding list ...
=IF(ISTEXT(D7);IF(SUM(IF(B7&C7&D7=$B:$B&$C:$C&$D:$D;1;0))>1;"Duplicate";"Unique");"")
Any solution for that to make it available? I now resticted it to rows 8-1000 and this works, but of course need to follow up the growth of the sheet!
answered Dec 6 at 9:50
Daniel H
1
great alternative formula, except it drives the CPU up to 90% and causes the sheet to become unusable if you expand the formula to the following "open end range" to make it applicable for an expanding list ...
=IF(ISTEXT(D7);IF(SUM(IF(B7&C7&D7=$B:$B&$C:$C&$D:$D;1;0))>1;"Duplicate";"Unique");"")
Any solution for that to make it available? I now resticted it to rows 8-1000 and this works, but of course need to follow up the growth of the sheet!
answered Dec 6 at 9:50
Daniel H
1
answered Dec 6 at 9:50
Daniel H
1
answered Dec 6 at 9:50
Daniel H
1
answered Dec 6 at 9:50
Daniel H
1
1
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.
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%2f1381052%2fexcel-check-if-combination-of-data-exists-in-a-certain-range%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
Use COUNTIFS() and compare the result with 1...
– Akina
Dec 5 at 16:41