Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I do simultaneous AND and OR filtering ?
I would like to do a filtering operation that is a combination of an AND
command and an OR command. In other words, I have a worksheet consisting of data in say, columns A, B, and C that I am requiring to be greater than zero. In another set of columns in the worksheet, say columns D, E, and F, I am requiring AT LEAST ONE of the entries to be greater than zero. So intuitively, the command structure would be something like =AND (A20, B20, C20) OR(D20, E20, F20), but I am not sure of the exact command syntax one would need to do the two things simultaneously. I plan to do this in a column with the heading €śFILTER€ť. It is my hope that by writing in the correct command structure, and filtering for €śTRUE€ť, I will be able to filter for all the rows that are completely free of non-zero entries in A, B, and C, and have at least one entry greater than zero in D, E, or F. Can anybody suggest the correct way to proceed? I plan to copy these filtered results into another worksheet. One thing I would like to know about filtering is when Excel does this, is it just like taking a photograph of the rows you have specified in the worksheet as they are, or does Excel do any formula recalculation based on the fractured and fragmented worksheet that now appears? In other words, this worksheet has a lot of operations Ive performed on it that are commands based on a range reference, such as VLOOKUP, SUMIF/(COUNTIF, and IF(ISNA(MATCH. I hope that when I do the filtering, Excel does not recalculate the values based on the fractured and fragmented ranges that appear "ex post facto" after the filtering has taken place. That would distort the results, and lead to errors and misinformation. Are there any precautions I must take to ensure that when I do the filtering, Excel copies the values as they are, and does not make any formula recalculations based on the fractured and fragmented range designations? Mick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I do simultaneous AND and OR filtering ?
You're pretty close:
=AND (A20, B20, C20, OR(D20, E20, F20)) Recalculation should not affect your data, the rows are only hidden so your formula references won't change (unless your formulae use Subtotal, which excludes rows hidden by a filter). When you paste the filtered data to a new sheet, my experience has always been XL will paste the values, not the formulae - so your pasted data will be a "snapshot". "Motown Mick" wrote: I would like to do a filtering operation that is a combination of an AND command and an OR command. In other words, I have a worksheet consisting of data in say, columns A, B, and C that I am requiring to be greater than zero. In another set of columns in the worksheet, say columns D, E, and F, I am requiring AT LEAST ONE of the entries to be greater than zero. So intuitively, the command structure would be something like =AND (A20, B20, C20) OR(D20, E20, F20), but I am not sure of the exact command syntax one would need to do the two things simultaneously. I plan to do this in a column with the heading €śFILTER€ť. It is my hope that by writing in the correct command structure, and filtering for €śTRUE€ť, I will be able to filter for all the rows that are completely free of non-zero entries in A, B, and C, and have at least one entry greater than zero in D, E, or F. Can anybody suggest the correct way to proceed? I plan to copy these filtered results into another worksheet. One thing I would like to know about filtering is when Excel does this, is it just like taking a photograph of the rows you have specified in the worksheet as they are, or does Excel do any formula recalculation based on the fractured and fragmented worksheet that now appears? In other words, this worksheet has a lot of operations Ive performed on it that are commands based on a range reference, such as VLOOKUP, SUMIF/(COUNTIF, and IF(ISNA(MATCH. I hope that when I do the filtering, Excel does not recalculate the values based on the fractured and fragmented ranges that appear "ex post facto" after the filtering has taken place. That would distort the results, and lead to errors and misinformation. Are there any precautions I must take to ensure that when I do the filtering, Excel copies the values as they are, and does not make any formula recalculations based on the fractured and fragmented range designations? Mick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I do simultaneous AND and OR filtering ?
Dear JMB:
Thanks, that worked fabulously! I checked for distortions in the column where I had the results of a SUMIF/(COUNTIF copied and dragged down the entire worksheet as far as there was data, and the numbers were identical in the filtered and non-filtered worksheets. If the range field had been broken up, and XL had recalculated, there surely would have been variation, which there wasn't. Thanks again for your help! Mick "JMB" wrote: You're pretty close: =AND (A20, B20, C20, OR(D20, E20, F20)) Recalculation should not affect your data, the rows are only hidden so your formula references won't change (unless your formulae use Subtotal, which excludes rows hidden by a filter). When you paste the filtered data to a new sheet, my experience has always been XL will paste the values, not the formulae - so your pasted data will be a "snapshot". "Motown Mick" wrote: I would like to do a filtering operation that is a combination of an AND command and an OR command. In other words, I have a worksheet consisting of data in say, columns A, B, and C that I am requiring to be greater than zero. In another set of columns in the worksheet, say columns D, E, and F, I am requiring AT LEAST ONE of the entries to be greater than zero. So intuitively, the command structure would be something like =AND (A20, B20, C20) OR(D20, E20, F20), but I am not sure of the exact command syntax one would need to do the two things simultaneously. I plan to do this in a column with the heading €śFILTER€ť. It is my hope that by writing in the correct command structure, and filtering for €śTRUE€ť, I will be able to filter for all the rows that are completely free of non-zero entries in A, B, and C, and have at least one entry greater than zero in D, E, or F. Can anybody suggest the correct way to proceed? I plan to copy these filtered results into another worksheet. One thing I would like to know about filtering is when Excel does this, is it just like taking a photograph of the rows you have specified in the worksheet as they are, or does Excel do any formula recalculation based on the fractured and fragmented worksheet that now appears? In other words, this worksheet has a lot of operations Ive performed on it that are commands based on a range reference, such as VLOOKUP, SUMIF/(COUNTIF, and IF(ISNA(MATCH. I hope that when I do the filtering, Excel does not recalculate the values based on the fractured and fragmented ranges that appear "ex post facto" after the filtering has taken place. That would distort the results, and lead to errors and misinformation. Are there any precautions I must take to ensure that when I do the filtering, Excel copies the values as they are, and does not make any formula recalculations based on the fractured and fragmented range designations? Mick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I do simultaneous AND and OR filtering ?
Glad that worked for you. Thanks for the feedback!
"Motown Mick" wrote: Dear JMB: Thanks, that worked fabulously! I checked for distortions in the column where I had the results of a SUMIF/(COUNTIF copied and dragged down the entire worksheet as far as there was data, and the numbers were identical in the filtered and non-filtered worksheets. If the range field had been broken up, and XL had recalculated, there surely would have been variation, which there wasn't. Thanks again for your help! Mick "JMB" wrote: You're pretty close: =AND (A20, B20, C20, OR(D20, E20, F20)) Recalculation should not affect your data, the rows are only hidden so your formula references won't change (unless your formulae use Subtotal, which excludes rows hidden by a filter). When you paste the filtered data to a new sheet, my experience has always been XL will paste the values, not the formulae - so your pasted data will be a "snapshot". "Motown Mick" wrote: I would like to do a filtering operation that is a combination of an AND command and an OR command. In other words, I have a worksheet consisting of data in say, columns A, B, and C that I am requiring to be greater than zero. In another set of columns in the worksheet, say columns D, E, and F, I am requiring AT LEAST ONE of the entries to be greater than zero. So intuitively, the command structure would be something like =AND (A20, B20, C20) OR(D20, E20, F20), but I am not sure of the exact command syntax one would need to do the two things simultaneously. I plan to do this in a column with the heading €śFILTER€ť. It is my hope that by writing in the correct command structure, and filtering for €śTRUE€ť, I will be able to filter for all the rows that are completely free of non-zero entries in A, B, and C, and have at least one entry greater than zero in D, E, or F. Can anybody suggest the correct way to proceed? I plan to copy these filtered results into another worksheet. One thing I would like to know about filtering is when Excel does this, is it just like taking a photograph of the rows you have specified in the worksheet as they are, or does Excel do any formula recalculation based on the fractured and fragmented worksheet that now appears? In other words, this worksheet has a lot of operations Ive performed on it that are commands based on a range reference, such as VLOOKUP, SUMIF/(COUNTIF, and IF(ISNA(MATCH. I hope that when I do the filtering, Excel does not recalculate the values based on the fractured and fragmented ranges that appear "ex post facto" after the filtering has taken place. That would distort the results, and lead to errors and misinformation. Are there any precautions I must take to ensure that when I do the filtering, Excel copies the values as they are, and does not make any formula recalculations based on the fractured and fragmented range designations? Mick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simultaneous subtotals | Excel Discussion (Misc queries) | |||
Simultaneous Filtering | Excel Worksheet Functions | |||
Simultaneous Excel backgrounds.... | Excel Discussion (Misc queries) | |||
simultaneous v&h lookup - approaches... | Excel Worksheet Functions | |||
solving for 2 unknowns using 2 simultaneous equations | Excel Discussion (Misc queries) |