Forum Discussion
Filtering Out Rows With Specific Text When Filtering With Multiple Criteria
I have a workbook to find duplicates in content that is housed on our site. This workbook has different worksheets that are combined into one using Power Query and each instance is tracked using an i...
Since SEARCH returns an error if the search text isn't found, you could use =ISERROR(SEARCH("Done", Duplicates[Notes])). This will be TRUE for any that don't include the word "Done".
I might be doing something wrong here. I added that to the end of my formula and it didn't work:
=FILTER(Duplicates,ISNUMBER(SEARCH(B1,Duplicates[Index]))+IF(B2<>"",ISNUMBER(SEARCH(B2,Duplicates[Index])))+IF(B3<>"",ISNUMBER(SEARCH(B3,Duplicates[Index]))),IF(B4<>"",ISNUMBER(SEARCH(B4,Duplicates[Index])*(ISERROR(SEARCH("Done",Duplicates[Notes]))))))
I think you can remove "ISNUMBER(SEARCH(B4,Duplicates[Index])*" from that part of the formula. Since ISERROR(SEARCH("Done", Duplicates[Notes])) will you TRUE if it's an error ("Done" not found), or FALSE if it's not an error ("Done" was found), there's no need for the ISNUMBER check.
I tried removing the B4 ISNUMBER part of the formula and it still yielded the same thing (Done was still appearing)
If you never want to include any rows that have "Done", then the part that checks for "done" should not be inside the part with IF(B4<>"",... If you have it in there, it will only exclude rows where the index is B4 and it says "done". Any rows that match the index in B1, B2, or B3 will be included even if the note says "done".
You will want to multiply the result of ISERROR(SEARCH("Done", Duplicates[Notes])) by all the other filter conditions, so you would need to put the other conditions inside parenthesis, so they get evaluated together, and then multiply by the last part.
When you do math on TRUE/FALSE, Excel converts TRUE to 1 and FALSE to 0. The FILTER function treats zero as FALSE and greater or less than 0 as TRUE. Any rows that need to be excluded need to result in a 0 when you take all the search criteria together. So conceptually it should be:
((Index matches B1)+(Index matches B2)+(Index matches B3)+(Index matches B4)) * (Notes contains "Done")So, if any of the Index conditions are TRUE, adding them together will give you a number greater than zero. Then if the Notes condition is FALSE, and you multiply the other conditions by it, the result will be 0 (FALSE) and the row will be filtered out.
Adding conditions together is like saying "if any of these is true", and multiplying is like saying "if all of these are true".