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?
-
1are 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ászOct 14, 2015 at 11:41
-
1Stack 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, 2015 at 11:42
-
Finding the complements between the two data sets– DavidPostill ♦Oct 14, 2015 at 11:44
-
You can use the Advanced Filter– Ron RosenfeldOct 14, 2015 at 12:11
-
@RonRosenfeld how?– Andi MohrOct 14, 2015 at 12:37
|
Show 1 more comment
3 Answers
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:
-
3
-
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? Oct 14, 2015 at 13:24
-
-
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, 2015 at 16:53
-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
