Advanced filter - exclude datarange

  • Thread starter Thread starter X-Ray
  • Start date Start date
X

X-Ray

I have a record list with serialnumber and an selection list with serials. I
am able to use the advanced filter to filter (show) only the records with the
serialnumber listed in de selection list.
Now I want to show all records with serialnumber which are NOTlisted in the
selection list.
Is this possible with advanced filters ?

As a workaround I delete the visible records when I have used the advanced
filter, but in a sheet with 40.000 records this takes a long time.
 
Debra Dalgleish shows the opposite (only show those rows that are on a list):
http://contextures.com/xladvfilter02.html#List

But you can modify it.
Use:
=COUNTIF(H:H,C2)=0

========
Personally, I'd add another helper column of formulas and then use plain old
autofilter.

I'd use a formula like:
=isnumber(match(a2,sheet2!a:a,0))
This will return True if A2 appears on Sheet2, column A.

Then I could autofilter to show True's or False's.
 
Back
Top