Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #593 Jun 15, 2012 www.energyefficientbuild.com.
user Dom_201 said:
Hello,
user Dom_201 said:
Hello,
I have been trying in vain to come up with a way to use FILTER to find and return questions and responses in a spreadsheet based on whether or not the "Tags" that were placed in the same row matched a specific set of "Tags" on the spreadsheet where the searching was being done from. The goal was to make it easier to find questions that had specific tags so that we could use the answers that had already been written when responding to those questions via Twitter instead of having to re-write them every time. I was able to make this work very easily when folks were only asking me to filter based on matching a single tag. But recently I have been asked to make this work so that they could narrow down the list of returns by using multiple tags. So, instead of returning results that matched one tag, now they want to return results that match multiple tags. I tried using FILTER to do this, but I couldn't figure it out. I also tried using QUERY, but couldn't get it that way either. I will share my spreadsheet so you can see what I tried, but try not to laugh too much at my attempts to make it work. :-/ I don't really know what I'm doing, I just keep trying different things until something finally works out...
Ok, so, here's a link to the spreadsheet:
I left it the way that it is, not trying to cover up any of my blunders in trying to make it work. :-/
If you look at the "Search" tab, you'll see that it worked just fine when I was only trying to filter based on one tag, but if you look on "Search 2" you'll see that all attempts that I made ended up failing...
Now, there are a lot of tabs in there, but you'll also see one called "Master" where I was basically going to just combine all of the Q&A tabs into one all encompassing tab and then just differentiate between the questions based on tags instead of creating multiple tabs.
So, the goal here is to be able to select one tag on the search 2 page and get a set of results, and then pick another tag on the search 2 page and narrow down those results, and another and another until I find the one I am looking for, or it no longer returns results because nothing matches that many tags.
I would truly appreciate any help you can offer. I have used these forums in the past and I know that some of you guys REALLY know your stuff!!! ;-) I have seen you help out so many folks, so if you could possibly help me too, I would be VERY grateful... Please? ;-)
Thanks a lot for your time,
Very respectfully,
Dominique
P.S. I just remembered to add: I am using Google Spreadsheets on an HP Windows 7 laptop using Chrome browser.
-----
Ok, I have put some sample data at the top of the "Master" tab for the purposes of clarifying what I am referring to....
I also made a few updates and cleared out all my old attempts since none of them worked anyways. I still have all of that stuff if you need it, but it was cluttering things up.
Now, the only page that I am really concerned about is "Search 2". The page "Search" was the first attempt and worked fine for what I was asked to deliver at the time. But, since more is being requested than "Search" could perform, I created "Search 2" and "Master". Thus, again, all I am worried about is "Search 2".
Speaking of Search 2, I added a column for "Link" because I want to return the Question, Answer, and Link for every applicable search.
Now, getting into expected results, I am going to put a few case scenarios and their explanations on the spreadsheet since that's the best way that I can think of to describe AND show you what I was hoping for. I will put CASE 1, CASE 2, CASE 3, etc in column A of Search 2 and refer to those cases here.
CASE 1 - Tags = "Parent" (Cell: A3)
In this case, the formula used would return the following questions (along with corresponding answers and links)
Am I eligible for other education benefits?
Where can I find more information about aid?
Who is involved in the college and financial aid process?
What is the Expected Family Contribution?
What is the FAFSA for?
What documents do I need to fill out a FAFSA?
It returns these questions because somewhere between column D and column T on the "Master" page, there was the tag "Parent". In this case, the instances of the tag "Parent" were typically in column F and G. However, I would like to be able to return questions / answers / links with the tag located anywhere between columns D and T. So, regardless of where that "Parent" tag is located between column D and T, if it matches the search, then the corresponding question / answer / link should be returned to the Search 2 page.
CASE 2 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3)
In this case, the formula used would return the following questions (along with corresponding answers and links)
Where can I find more information about aid?
What is the Expected Family Contribution?
What is the FAFSA for?
What documents do I need to fill out a FAFSA?
In this case, there are fewer results because fewer questions had the tags necessary to meet the search parameters (as I am calling them). So, logically speaking, by setting A3 to equal "Parent" and B3 to equal "Apply" I am telling the formula to return to me all of the questions with corresponding answers and links that have both the tags "Parent" AND "Apply".
In each CASE, as a new search term is added, fewer results would be returned and the list would become smaller.
CASE 3 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3) "taxes" (Cell: C3)
In this case, the formula used would return the following question (along with corresponding answer and link)
What documents do I need to fill out a FAFSA?
Because this question was the only one to meet all the search parameters, it is the only one that is returned. So, logically, by setting A3 to "Parent", B3 to "Apply", and C3 to "taxes" I am telling the formula to return to me all of the questions with corresponding answers and links that have the tags "Parent" AND "Apply" AND "taxes".
CASE 4 - Tags = "Parent" (Cell: A3) "Apply" (Cell: B3) "taxes" (Cell: C3) "Receive" (Cell: D3)
In this case, nothing is returned. There are no questions that have all four of the chosen tags, so no return is required.
Basically, this whole thing is to be set up as a way to narrow down the list easily to only questions that pertain to a specific set of topics. So, if we want to respond on Twitter to a parent who doesn't know much about the FAFSA application process, and has asked us about what tax documents are required to fill out the form properly, we can narrow down a very large list of frequently asked questions and previously compiled responses to just those responses that pertain to the question being asked.
I really hope this explains what I am trying to do. Basically, if a question has the tags in cells A3 AND B3 AND C3 return question/response/link for each question that meets the parameters...
Thank you SOOOOOO much!!!
Again, sorry for the delay. Please, let me know if there is anything I was unclear about.
Very respectfully,
Dominique Ramirez
----------------------------------------------------------------------------------------------
in the following solution I created 4 separate sheets for each of the search cases
No comments:
Post a Comment