Create "other" filter
 

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!

Create "other" filter
 

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

On Nov 30, 6:16 am, stocktsi
wrote:
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!

Create "other" filter
 

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" wrote:

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

On Nov 30, 6:16 am, stocktsi
wrote:
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!

Create "other" filter
 

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

On Nov 30, 4:44 pm, stocktsi
wrote:
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" wrote:
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

On Nov 30, 6:16 am, stocktsi
wrote:
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!- Hide quoted text -

- Show quoted text -

Create "other" filter
 

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" wrote:

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

On Nov 30, 4:44 pm, stocktsi
wrote:
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" wrote:
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

On Nov 30, 6:16 am, stocktsi
wrote:
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!- Hide quoted text -

- Show quoted text -

Create "other" filter
 

You're welcome - thanks for feeding back.

Debra Dalgleish has some detailed notes on advanced filter he

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

Hope this helps.

Pete


On Nov 30, 6:30 pm, stocktsi
wrote:
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" wrote:
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

On Nov 30, 4:44 pm, stocktsi
wrote:
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" wrote:
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

On Nov 30, 6:16 am, stocktsi
wrote:
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -