Create "other" filter

[stocktsi]

I've got a table of data that I'm working with and trying to get displayed
the way I need it. There's a "Status" column that has one of 4 valid status
values in it. I've used autofilter to see individual cuts of each status,
but I'd also like to be able to set up a filter that would allow me to see
all non-valid status at the same time without having to specify what they are
(ie - show me any row where the status isn't one of the 4 valid options).

For example:


Status (in column N)
-------
New
Hold
Active
Close
sales
postpone

So the advanced filter would allow me to see all rows with "sales" or
"postpone" as a status.

Thanks for your help!

 

[Pete_UK]

On your filter pull-down you can select Custom (usually the 3rd option
down), then you can select two criteria.

First criteria: Is equal to | sales
click the OR button
Second criteria: Is Equal to | postpone
click OK

Alternatively, you could use a helper column and use a code like "non-
valid" if the other column contains sales or postpone, and filter on
the helper column.

Hope this helps.

Pete

 

[stocktsi]

Pete,

The issue is that I want to capture ALL status codes that aren't one of the
4 listed. There will be more than 2 (which is all the Custom option allows),
and I don't know what all of them will be up front.

I thought about creating an expression to determine this as part of the
advanced filtering, but the idea of a helper column might be easier. The
"clean" way would be to create a true/false expression if the data wasn't in
a list (as opposed to creating a long If statement which would be ugly to
maintain when I add other valid status codes.). Is there a way to create a
simply expression like:

if A2 is not in the set of ("New", "Hold", "Active", "Close") or

if A2 is not in the set of (X1:X4), and put the codes in cells X1 - X4

Thanks.

 

[Pete_UK]

In a spare part of your sheet (eg X1:X4) just list your valid codes -
this approach makes it easier to add to them in future. Then in your
helper column you can have a formula like:

=IF(ISNA(MATCH(status,X$1:X$4,0)),"invalid",status)

where status is your first status cell, eg A2, or D2. Copy this down,
then you could apply your filter to this column to get all the invalid
codes together.

Hope this helps.

Pete

 

[stocktsi]

Pete,

That worked great. Thanks for your help!

I'm also curious to understand if this is possible to do with advanced
filters instead of adding an additional column. I use autofilters a lot, but
haven't used advanced filters before so I'm trying to understand how they
work, and when you would want to use them.

Thanks again.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Debra Dalgleish has some detailed notes on advanced filter here:

http://www.contextures.com/xladvfilter01.html

Hope this helps.

Pete