Google Sheets Query Function - Part II
HTML-код
- Опубликовано: 27 авг 2024
- Learn how to master the most powerful formula in Google Sheets, they Query function.
This video is taken from my free Google Sheets course: Advanced Formulas 30 Day Challenge:
courses.benlco...
I watch this on 2020. Thank you very much for it...
Thanks
Hi Ben, I have few question and I've been searching for answer all over so might as well try my luck here. my question every time i include special character in my where clause as my filter, i dot get any result. like fraction "1/2 or or Celsius symbol or arithmetic symbol. I'd like to know if there are list of special character that are off-limits to use in where clause as filter or if there are way around to it. Hope to hear from you.
Very useful! Thank you!
how can I take the picture from other sheet by "Query" command?
Great video! I have a dataset with station codes, location, date and measured values of dissolved oxygen (DO), ammoniacal nitrogen, etc. Is it possible to extract the average value of DO by month? I need to correct some cases missing, and, due sazonality, the month is something important. I only managed the avg of all dates. Thank you!
NICE, this helped.
-- LONG QUESTION --
Let's say I had a data pull using "insertdata(), or inserthtml()" that stored raw data on one worksheet. I then create a query that would pull in one cell based on a column value of the matching cell but the query is stored on another worksheet.
WS1_RAW_DATA - this is the first worksheet that does the inertdata() / inserthtml() and what it looks like after the inserthtml() or insertdata() runs ...
Col_A Col_B
APP 22.00
MSFT 33.00
RHD 123.00
WS2_WORKING_DATA - this is my working worksheet that has a table that I write a query in one of the columns that looks like this ...
Col_A Col_B
APP '=query(WS1_RAW_DATA!A:B, "select b where A like '%AP'", 0)
For row 2 above, column b I've entered the above formula, and that works great! Thanks for clearing this up!! :D
"select b where A like WS2_WORKDING_DATA!A" ????
The goal here is not to have to type in modulo % [string] but use column As' value in WS_WORKING_DATA as part of the query? Possible - or madness?
Fabulous
can I use QUERY function like filter with WHERE using the value was chosen in list of possible values? in fact, the conditions should be somewhat more complicated, but the point is in principle. Is it possible to *pass a parameter from a cell to the QUERY function* ? UPD: It's possible using such construction: "select B where B = '" & H1 & "'"
BIG Thks !
What if some columns returned from a query turn out empty, and I'd like to hide such empty columns from being displayed? Of course I don't know which columns would that be, because that depends on the data and on the conditions in the query. Is there any way to detect columns that are completely empty and remove them from the result somehow?
Another question is how can I format the result as a nice table, with backgrounds, borders around it & stuff, but in such a way so that it automatically fit the data returned from the query? (Kinda like how the Pivot Tables work.)
Thanks for the video, helped me a lot!.... Query of a Query... who would have thought lol the only way I could get my Query to work using multiple conditions (Drop down lists) Cheers..
wonderful video
Yes, parameters written in certain cells is useful. Can you do it?
Query is really great. Thanks for sharing. Is there a way to join (as in SQL JOIN) two ranges/sheets together?
=QUERY({sheet1!A:D;sheet2!A:D}; where ..............
so powerful,,,thanks mate
Hello - do you still advise on google sheets? I have something that I've been struggling with for a while
thanks man
Pls help me to fetch TXT and num value, which are in single column,by query fn. It fetch only num value not TXT.. pls help..
How to include COUNTIFS formula in Query function
I have tried building my query with AND & also OR query but I kept getting my result wrong. How to stop an OR query working when there are also AND query to limit them?
Anyone have any ideas how to just add column in group by query that is not number?
Hi, very helpfull videos.
In more details:
I have a problem that I can not think of a solution. I have data set witch needs to be group by.
My problem is that I need to add extra columns to my group by query (extra data I use to see what is going on with product that I'm grouping and that extra columns are text), I just need to COPY data from multiple columns in every first row of each unique item of 'query group by' column.
Hope I explained it well enough.
Thx,
Cheers
Hi, may I hire you to build a Google Sheet for me?
I can refer you to my partner who can help. Please start here: docs.google.com/forms/d/e/1FAIpQLSeonuwyo6V1L0Mkm2THG5w6cTBqrSVGER4gLukZMmPKf8QvAQ/viewform