Monday, October 29, 2012

Google Spreadsheet advanced query formula


Question:

hiya, i'd like to be able to filter a list using logical OR on a series of terms i type into a cell. I guess i can do this with the SPLIT and ARRAYFORMULA functions, but how? Here is an example search with a single search term.

Would be great if someone could give me the correct arrayformula to search multiple terms. Feel free to copy & edit the doc.

Thanks
Alex


.......

Thanks, though this is not what i'm looking for (but i would use a query instead if the google sytnax allowed creation of new fields or complex where clauses, but it doesn't seem to)

I want to filter the list in sheet "datasheet" according to a series of search terms. So for example entering "red, green" into cell B1 of sheet "filtersheet" would return 5 items.

Thanks



Solution:


Suppose you have following data in 'datasheet':





Then put the following formula in Cell A3 of 'filtersheet':

=query(datasheet!A1:Z;"select * where B contains '" & join("' or B contains '";arrayformula(trim(split(B1;",")))) &"'")



Then you will get the following output on the 'filtersheet':





I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private or confidential projects:


If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


Thanks,
Kishan,

1 comment:

  1. That's brilliant, thankyou. Very neat and clear.

    ReplyDelete