How to do an Except filter in Excel as a set operation?
Say I have two sets:
1
2
3
4
5
6
and
3
6
I want to be able to get the first one except the second, e.g. 1
2
4
5.
How could I do that?
microsoft-excel worksheet-function
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
|
show 1 more comment
Say I have two sets:
1
2
3
4
5
6
and
3
6
I want to be able to get the first one except the second, e.g. 1
2
4
5.
How could I do that?
microsoft-excel worksheet-function
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
1
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
1
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37
|
show 1 more comment
Say I have two sets:
1
2
3
4
5
6
and
3
6
I want to be able to get the first one except the second, e.g. 1
2
4
5.
How could I do that?
microsoft-excel worksheet-function
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
Say I have two sets:
1
2
3
4
5
6
and
3
6
I want to be able to get the first one except the second, e.g. 1
2
4
5.
How could I do that?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
edited Oct 14 '15 at 13:11
Gary's Student
13.5k31730
13.5k31730
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
asked Oct 14 '15 at 11:36
It'sNotALie.It'sNotALie.
11315
11315
1
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
1
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37
|
show 1 more comment
1
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
1
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37
1
1
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
1
1
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37
|
show 1 more comment
3 Answers
3
active
oldest
votes
As stated by a few people, you can use an advanced filter option. See below as an example of how to set up multiple rules:
l
Using a helper column:
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
add a comment |
To use the Advanced filter, a formula one could use might be:
=SUMPRODUCT(--AND(A8<>{3,6}))>0
or
=SUMPRODUCT(--AND(A8<>Exclude))>0
where Exclude is a Name'd range that includes all of the members to be excluded.
Note that with the Advanced Filter, you can elect to copy the results to another location, or filter the list in place. And if you use a range instead of an array constant in the formula, the set to be excluded can have any number of members.
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
add a comment |
-Suppose set 1 and 2 are on columns A and B
-Add header row as first row
-Add this formula in C2 '=VLOOKUP(A2,$B$2:$B$1000,1,FALSE)' and fill out on all rows of C column (works for 1000 values)
-Set filter on first row via Menu/Home/Sort&Filter and select only #N/A values in third column
-Now the first column will only contain values not found in the second column
answered Jan 5 at 18:45
XPloRRXPloRR
813
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%2f986525%2fhow-to-do-an-except-filter-in-excel-as-a-set-operation%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
As stated by a few people, you can use an advanced filter option. See below as an example of how to set up multiple rules:
l
Using a helper column:
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
add a comment |
As stated by a few people, you can use an advanced filter option. See below as an example of how to set up multiple rules:
l
Using a helper column:
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
add a comment |
As stated by a few people, you can use an advanced filter option. See below as an example of how to set up multiple rules:
l
Using a helper column:
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
As stated by a few people, you can use an advanced filter option. See below as an example of how to set up multiple rules:
l
Using a helper column:
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
edited Oct 14 '15 at 13:36
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
answered Oct 14 '15 at 13:06
Eric FEric F
2,79031331
2,79031331
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
add a comment |
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
2
2
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
(+1) just for the animation.
– Gary's Student
Oct 14 '15 at 13:12
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
I think he has posted just a sample dataset, how he can exclude more than 3 values? How it will be automatically updated based on the second range?
– Máté Juhász
Oct 14 '15 at 13:24
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
@MátéJuhász Same thing would work if you had a helper column.
– Eric F
Oct 14 '15 at 13:33
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
Updated to show both ways.
– Eric F
Oct 14 '15 at 13:36
add a comment |
To use the Advanced filter, a formula one could use might be:
=SUMPRODUCT(--AND(A8<>{3,6}))>0
or
=SUMPRODUCT(--AND(A8<>Exclude))>0
where Exclude is a Name'd range that includes all of the members to be excluded.
Note that with the Advanced Filter, you can elect to copy the results to another location, or filter the list in place. And if you use a range instead of an array constant in the formula, the set to be excluded can have any number of members.
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
add a comment |
To use the Advanced filter, a formula one could use might be:
=SUMPRODUCT(--AND(A8<>{3,6}))>0
or
=SUMPRODUCT(--AND(A8<>Exclude))>0
where Exclude is a Name'd range that includes all of the members to be excluded.
Note that with the Advanced Filter, you can elect to copy the results to another location, or filter the list in place. And if you use a range instead of an array constant in the formula, the set to be excluded can have any number of members.
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
add a comment |
To use the Advanced filter, a formula one could use might be:
=SUMPRODUCT(--AND(A8<>{3,6}))>0
or
=SUMPRODUCT(--AND(A8<>Exclude))>0
where Exclude is a Name'd range that includes all of the members to be excluded.
Note that with the Advanced Filter, you can elect to copy the results to another location, or filter the list in place. And if you use a range instead of an array constant in the formula, the set to be excluded can have any number of members.
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
To use the Advanced filter, a formula one could use might be:
=SUMPRODUCT(--AND(A8<>{3,6}))>0
or
=SUMPRODUCT(--AND(A8<>Exclude))>0
where Exclude is a Name'd range that includes all of the members to be excluded.
Note that with the Advanced Filter, you can elect to copy the results to another location, or filter the list in place. And if you use a range instead of an array constant in the formula, the set to be excluded can have any number of members.
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
answered Oct 14 '15 at 16:53
Ron RosenfeldRon Rosenfeld
1,9992611
1,9992611
add a comment |
add a comment |
-Suppose set 1 and 2 are on columns A and B
-Add header row as first row
-Add this formula in C2 '=VLOOKUP(A2,$B$2:$B$1000,1,FALSE)' and fill out on all rows of C column (works for 1000 values)
-Set filter on first row via Menu/Home/Sort&Filter and select only #N/A values in third column
-Now the first column will only contain values not found in the second column
answered Jan 5 at 18:45
XPloRRXPloRR
813
add a comment |
-Suppose set 1 and 2 are on columns A and B
-Add header row as first row
-Add this formula in C2 '=VLOOKUP(A2,$B$2:$B$1000,1,FALSE)' and fill out on all rows of C column (works for 1000 values)
-Set filter on first row via Menu/Home/Sort&Filter and select only #N/A values in third column
-Now the first column will only contain values not found in the second column
answered Jan 5 at 18:45
XPloRRXPloRR
813
add a comment |
-Suppose set 1 and 2 are on columns A and B
-Add header row as first row
-Add this formula in C2 '=VLOOKUP(A2,$B$2:$B$1000,1,FALSE)' and fill out on all rows of C column (works for 1000 values)
-Set filter on first row via Menu/Home/Sort&Filter and select only #N/A values in third column
-Now the first column will only contain values not found in the second column
answered Jan 5 at 18:45
XPloRRXPloRR
813
-Suppose set 1 and 2 are on columns A and B
-Add header row as first row
-Add this formula in C2 '=VLOOKUP(A2,$B$2:$B$1000,1,FALSE)' and fill out on all rows of C column (works for 1000 values)
-Set filter on first row via Menu/Home/Sort&Filter and select only #N/A values in third column
-Now the first column will only contain values not found in the second column
answered Jan 5 at 18:45
XPloRRXPloRR
813
answered Jan 5 at 18:45
XPloRRXPloRR
813
answered Jan 5 at 18:45
XPloRRXPloRR
813
answered Jan 5 at 18:45
XPloRRXPloRR
813
813
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%2f986525%2fhow-to-do-an-except-filter-in-excel-as-a-set-operation%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
are those sets in columns? You can e.g. get a helper column to check whether numbe is included in second set and filter in that helper column.
– Máté Juhász
Oct 14 '15 at 11:41
1
Stack Overflow duplicates: How can we perform common set operations (union, intersection, minus) in MS Excel?, What is the most efficent way to get a Range "set difference" in Excel Automation?
– DavidPostill♦
Oct 14 '15 at 11:42
Finding the complements between the two data sets
– DavidPostill♦
Oct 14 '15 at 11:44
You can use the Advanced Filter
– Ron Rosenfeld
Oct 14 '15 at 12:11
@RonRosenfeld how?
– Andi Mohr
Oct 14 '15 at 12:37