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 index key to specify what each is a duplicate of and of which type. From there, I used the Power Query output table name (Duplicates) to create a dashboard, that has search boxes to help narrow down the list so it's easier to find the related duplicates.
I'm using the FILTER function to filter the table according to the indexes at the top and ignoring the second, third, and fourth indexes if the search fields are blank. I'm sure there's a better way to do that but it was the only way I could get it to 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]))))
There is a Notes column added at the end of the table that I am trying to target so any items that are marked as "Done" are not in this list. I've tried amending the FILTER function with NOT(ISNUMBER(SEARCH("Done", Duplicates[Notes]))) and using an * or + but it just causes a VALUE error. I have also tried adding ISNUMBER(SEARCH("", Duplicates[Notes])) with an * or + as well.
Due to the nature of this content, I cannot share the source file so if I can provide any other clarifying information, please let me know.
https://support.microsoft.com/en-us/office/regextest-function-7d38200b-5e5c-4196-b4e6-9bff73afbd31
pattern
keywords1|keywords2|ketwords3
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.
