Forum Discussion
Using the FILTER Function
The purpose of this list of words is to identify common words/terms and their definitions used in our Quality Management System documents. We are working on a system that simplifies our procedures where the reviewer can refer to another document to provide necessary definitions. Basically, we are removing definitions from our documents and instead have ONE document that provides all definitions. This allows for consistency within our documentation as well as ensuring that our definitions across our organization meets regulatory requirements. In order to ensure that we have captured all definitions, we are reviewing each document and comparing included definitions to the list of words to see if that word/term is already included in our list. I am trying to use the FILTER function in conjunction with setting up a search bar so that I can just enter a word or term in the search bar, rather than going to FIND and scrolling through the list, and have Excel search the list and come back with a list where that word/term is included. For example, let's say my list includes: Asset, Asset Management, Asset Negotiations, Liability, Liability Management. I want to be able to enter the word "Asset", in the search bar and have Excel provide a separate list that would include: Asset, Asset Management, and Asset Negotiations, or enter a part of the word, e.g., "Aud" and have it come back with Audit, Auditor, etc. Can this be done?
First off, being in Quality and Regulatory for medical device companies, I feel your pain and commend your efforts.
That said, my prior post still applies the best that I can tell. Here is an example using the 'quick filters':
notice how I typed "Process" in and only the 2 lines are shown in the box below it and if I click OK the list is filtered to only those 2 lines
And here is an example using the FILTER function:
notice how that returns a separate list (could be on a different tab if you want) as opposed to filtering the view on the current list. You can also see in the formula bar I tweaked the formula to search for the search term in E1 to be found in EITHER column A or column B. Note I used the perior (.) character after the colon in the ranges to tell excell to only use the used range in that area.
If you instead define the original data as a Table (Home -> Format as Table) then you can use table references instead of cryptic ranges. Let's say you name the Table: "tblTerms" then it would look like:
=FILTER(tblTerms, ISNUMBER(SEARCH(E1, tblTerms[Term]))+ISNUMBER(SEARCH(E1, tblTerms[Definition])), "none found")
like this:
I hope this is helpful.