Filter using "not equal to" criteria using array

[AlaaAlii]

Hello. My current filter is this:

Please Login or Register  to view this content.

The "StaffArray" contains 29 entries.
What I want is to filter and show whatever is NOT in the staff array. I tried all sorts of syntax like:

Please Login or Register  to view this content.

But all of them returned errors.
From doing some search, people were mentioning something along the lines of "use a formula column to determine whether to include or exclude the values". Maybe they mean this: create some kind of formula that uses the "StaffArray" and searches the field/column in question, and then creates another array containing the values that are not the same as in "StaffArray"... Am I making any sense? If yes, then how do I do this?

I'm only looking for a solution; i.e. it doesn't have to be optimized to run fast.

[kvsrinivasamurthy]

PL try this.

Please Login or Register  to view this content.

[AlaaAlii]

No, you misunderstood. I'm filtering on two columns: 12 and 9. The 12 one is just OPEN, PREAPPROVED and WORKINPROGRESS, I have no issues with that. But the second column/field, field 9, is being filtered using an array called "StaffArray". What I want is to filter this field, field 9, and criteria would be NOT equal to StaffArray.

I'll edit the first post to remove that first filter since it's irrelevant.

[AlaaAlii]

Anyone?
Well, I gave in, and I thought I'd just do something like what kvsrinivasamurthy said.
I did...

Please Login or Register  to view this content.

...up until Name29. But it didn't work =(, it gives an AutoFilter Method of Range Class Failed error.

[JosephP]

you can't do that with an autofilter. you'd need to use an advanced filter instead

[AlaaAlii]

How do I do that? How do I use it to show values that are NOT equal to certain values?

[JosephP]

you need a criteria range of two cells. the top one is blank and then use a formula like this in the second cell:
=isna(match(I2,data_range,0))
where I2 is the first cell of data to be tested and data_range is a range containing the values to exclude.

[AlaaAlii]

Ok, I'm stuck. I understand the basics of how advanced filters work. Your formula returns a TRUE (if the cell being tested does not equal to the range of values to exclude), else it returns FALSE. If I run the advanced filter on this, it's just looking for values with TRUE or FALSE in them. Also, you said to leave the top cell blank (in the criteria range); wouldn't that essentially make the advanced filter show everything?

EDIT: well I've got an idea. Using your formula: if it returns true, copy the value of the data being tested into an array. Then move to the next cell, and if it's true, copy the data to the array as well. So, in the end, I'll have an array that I can use using the normal autofilter, get it?
If this will work, I need help as well =/, because I'm completely new to VBA.

[JosephP]

Ok, I'm stuck. I understand the basics of how advanced filters work. Your formula returns a TRUE (if the cell being tested does not equal to the range of values to exclude), else it returns FALSE. If I run the advanced filter on this, it's just looking for values with TRUE or FALSE in them. Also, you said to leave the top cell blank (in the criteria range); wouldn't that essentially make the advanced filter show everything?

your criteria range is two cells only. the top one is blank and the bottom one has that formula in it. using those as the criteria the filter will evaluate for each row and hide any where the result is false

EDIT: well I've got an idea. Using your formula: if it returns true, copy the value of the data being tested into an array. Then move to the next cell, and if it's true, copy the data to the array as well. So, in the end, I'll have an array that I can use using the normal autofilter, get it?
If this will work, I need help as well =/, because I'm completely new to VBA.

yes you could also use an additional formula column and simply autofilter for true in addition to your other column filter.