Trying to filter out substring from another sheet in google-spreadsheet (edit: Now with only SOLUTION/FORMULA condensed to one Sheet )

0
votes

Problem Description:

  • I have a master-Sheet which is fed by incoming information from a google-form
  • It contains sensitive information like phone numbers so I have imported it to another workbook with only desired details like Date,Member-Name and College-Name

I went with the rather simplistic approach of importing like this: =Importrange("MY_SHEET_KEY","Sheet1!A1:H1024")

  • On the other sheet of the same workbook, I have attemped this and failed: =filter(Sheet1!A1:H1024,Sheet1!E1:E1024="*"&"National"&"*")

I actually want to filter out "National College of Information Technology" from a list of colleges with the formula given above.

FOR REFERENCE:

this works:

=filter(Sheet1!A1:H1024,Sheet1!E1:E1024="NO")

however, this FAILS to work:

=filter(Sheet1!A1:H1024,Sheet1!E1:E1024="*"&"N"&"*")

Please give me an example of a formula that allows me to filter the students of "National College of IT", "Himalayan College of IT" or "St Lawrence College". I think finding sub-string "National", "Himalayan" and "Lawrence" is quite sufficient for my purposes.

I have referred several posts in stackoverflow to hit nothing what am I doing wrong?

EDIT1:

Okay I managed to overcome the filtering process bu using Query() function. However google-sheets query seems to be case sensitive. I am trying to find a work-around.

For example:

This works:

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where (C contains 'NCCS' OR C contains 'National') ")

but it FAILS to show me if the field has entry in the form of "nccs" or "national college". I would like it to disregard case and process whatever case people throw at it.

Right now, if the college name contains "NCCS" or "National" as sub-string, only then, it is working. And it is also Case-sensitive.

EDIT2:

The solution given by Ed Nelson was perfect for my current problem! Thanks a lot!

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

NEW PROBLEM

I am now modifying the question to address a new problem

  • The solution provided so far has managed to address problems with data filtering with two Sheets(Sheet1 and Sheet2)
  • Sheet2 acquires data from master-Sheet from another google-workbook
  • This is done so that sensitive information like Phone number is filtered out
  • I am supposed to SHARE these sheets with people and I would not like to give out more information than necessary

  • My current setup allows me to do this by applying this formula in Sheet1

    =Importrange("MY_SHEET_KEY","Sheet1!A1:H1024")

  • and this formula in Sheet2

    =query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

  • However, as I am sharing a workbook, those who get the link are easily able to see Sheet1 also, which contains information on everyone without filtering.

  • I would like to know how these two formulas can be condensed into just one formula for a single spreadsheet (Note: My previous similar attempt with filter() has landed me in a recursive definition warning and it failed to process.)

2
google-sheets
Questions on this site are not only for short-term solutions to your problem but also to provide answers for other users in the future who face the same or similar questions. To achieve this, it is important to keep questions and answers synchronized with each other and focused on specific issues. You can always edit a question to add more information and clarify it. However, after an answer addresses your question you should not change the meaning of your question. Instead, please ask a new question for the new topic or issue (and link to the previous question for context if it makes sense). – Rubén
^^^ was taken from New Help Center article for constant changing questions – Rubén
@Rubén: I am new to stackoverflow and this is my first question. Thanks for the information but haven't I clearly marked every answer I got at every phase of the questions I asked? Please re-read my whole queston-description again. I've certinly marked clear answers for every question that I have asked. Also there is no concern for keywords as I have simply added modifications inside the "("bracket")" so that anyone who faces this problem in the future will most likely arrove to this same solution. peace out – Siddhant Rimal
Question posts in Stack Overflow should have one and only one question and should not have "phases" . Instead of adding the answers to the question post an answer post should be posted. In other words, 1) use question posts to ask questions, 2) use answers posts to post the answer to a question 3) If you have a new question, post it as a new question post. – Rubén
Trying to filter out substring from another sheet in google-spreadsheet is my original Question and THIS is my modified one : Trying to filter out substring from another sheet in google-spreadsheet (edit: Now with only SOLUTION/FORMULA condensed to one Sheet ) . You can take it as a whole question. My intent was to only add quality to the original question. Distributing the question into two would have surely attracted low quality answers. Moreover, anyone who has this problem in the future will now see a very detailed overview on how to solve this problem. – Siddhant Rimal

2 Answers

1
votes

NEW PROBLEM ANSWER. Yes, you can combine importrange and query. Note that the columns are referenced by Col3 (equals C). This seems to be required.

=query(Importrange("MY_SHEET_KEY","Sheet1!A1:G1024"),"select * where lower(Col3) contains lower('NCCS') OR lower(Col3) contains lower('National')")
1
votes

To overcome the case problem try:

=query(Sheet1!A1:H1024,"select A,B,C,D,E,F,G where lower(C) contains lower('NCCS') OR C contains lower('National')")

Yes, ImportRange and Query can be combined. Note in the query Col refeferences must be used. Column C is Col3.

=query(Importrange("MY_SHEET_KEY","Sheet1!A1:G1024"),"select * where lower(Col3) contains lower('NCCS') OR lower(Col3) contains lower('National')")