Say I have two sets:
1 2 3 4 5 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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
To use the Advanced filter, a formula one could use might be:
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.
-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