Forum Discussion
FILTER with SEARCH Function Question
Good day,
I have a spreadsheet with two tabs. In one tab (called "QUERY"), I have the dataset I'm working with, which is coming from Azure Devops via the AzureDevops "Team" integration. So anytime I refresh, the data is likely to change. The table for that dataset is called "VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1."
In the other tab (called "RIs"), I'm using the FILTER function to pull in rows of data in which a specific keyword exists in the "Tags" column in the table referenced above.
I have been successful in pulling in all columns of data (A thru E) for a row where I have a keyword match using this formula:
=FILTER(VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1, ISNUMBER(SEARCH("Reviewed",VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[Tags])))
What I have not been able to figure out is how to pull in only a few columns (A thru C). In other use cases I have used a slightly different FILTER formula to gather columns (A thru C), but not with using the SEARCH function to find partial matches for a keyword, but rather matching a "State."
=FILTER(QUERY!A:A:QUERY!C:C,((QUERY!D:D="Review Complete")))
My question is if it's possible to use the search and filter functions combined to display only the first 3 columns when a keyword is found in the "Tags" column.
Screen shots below.
Image 1 - (Tab: "QUERY") dataset coming from AzureDevops
Image 2 - (Tab: "RIs") table in the tab that I am importing that data into using the filter function.
I don't post often, so I'm hoping I provided enough information to make sense of my request.
Appreciate any guidance you can provide.
Thank you.
NSUMike When you select the entire table as the filter array, all columns will be returned by FILTER. Extend the array code by the first and last column you want to display for.
In you case that would become:
VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[[ID]:[Full Name]]
If you would want to return only the first and third column, use your original formula but wrap it in another FILTER adding a "mask" of columns that should be returned between curly brackets. Any number not equal to zero=include, 0=exclude). Something like this:
=FILTER(FILTER(..................),{1,0,1,0,0})
2 Replies
- Riny_van_EekelenPlatinum Contributor
NSUMike When you select the entire table as the filter array, all columns will be returned by FILTER. Extend the array code by the first and last column you want to display for.
In you case that would become:
VSTS_4914e127_bdaf_4bc3_8508_05512d20d6a1[[ID]:[Full Name]]
If you would want to return only the first and third column, use your original formula but wrap it in another FILTER adding a "mask" of columns that should be returned between curly brackets. Any number not equal to zero=include, 0=exclude). Something like this:
=FILTER(FILTER(..................),{1,0,1,0,0})
- NSUMikeCopper ContributorFantastic! That worked like a charm. I learn a little bit more every day. Thank you so much.