adv filter

[b166er]

hi members,

i want to filter data through adv filter that does not equal to
specific criteria.

i used criteria A1<> JOB335
A2<> JOB339
A3<> JOB350
to show only those jobs which are not equal to that criteria.
but that did not work is there any other way through which i could give
criteria does not equal to specific text or number or alphanum. keep in
mind there are thousands of jobnumbers i have to work with.

 

[somethinglikeant]

Have you used the OR relationship for your criteria (AND wont work)
I set the range up as below.
Job<>JOB335 OR <>.....etc

just an idea

somethinglikeant


Job Job Job
<>JOB335 <>JOB339 <>JOB350

 

[Bob Phillips]

You need to setup a criteria range, first row with the data heading, and
second row of ="<>"&A1. Then add another column with same first row, and
second row = ="<>"&A2.

Then use that criteria in advanced filter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Debra Dalgleish]

In a blank column, enter the job numbers that you want to exclude.
In another column, leave the cell in row 1 blank
In row 2, enter a formula that refers to the column of excluded job
numbers, and the first job number in the table that you're filtering, e.g.:

=COUNTIF(N:N,F2)=0

where N is the column with excluded job numbers, and F2 is the first job
number in the table.

Run the advanced filter, and for the criteria range, select the blank
heading cell, and the cell with the CountIF formula.