Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a scatter chart with 2 "X" values with common "Y"s | Charts and Charting in Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions |