1

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?

6
6

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:

enter image description herel

Using a helper column:

enter image description here

4
  • 3
    (+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
0

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.

enter image description here

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.

0

-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.