Google Sheets QUERY Function Tutorial - SELECT, WHERE, LIKE, AND, OR, LIMIT statements - Part 1
ฝัง
- เผยแพร่เมื่อ 26 พ.ย. 2024
- Video tutorial series about QUERY function in Google Sheets.
In this tutorial we'll cover how to use QUERY function in Google Sheets to pull filtered results out of your main dataset. We'll use SELECT, WHERE, LIKE, AND, OR and LIMIT statements to filter data by number value, text & text that contains a certain string in it.
Google Sheets
www.google.com...
Website:
www.chicagocom...
![[#2024MAMA] ROSÉ (로제), Bruno Mars - APT. | Mnet 241122 방송](http://i.ytimg.com/vi/dgGqD28J6aQ/mqdefault.jpg)
EXCELLENT VIDEO THIS IS EXACTLY WHAT I NEED!!!
Now I understand more the power of GSheets,
I enjoy 19:29 minutes, super simple and straight forward. Super valuable, I love it!
This is one of this video that I saved in my own private playlist.
Query functions are amazing!
Thanks!
Amazing! I just built a query from client data to show the client, their preferences, and plan type for clients who are either recent starts or restarts so my team can give extra attention to our newbies. Thank you!
Just a suggestion. Maybe share your data sheet in the about section so that people can follow along using the queries.
Great video!
After 2 weeks of searching, this is exactly what I wanted! Thanks so much!!!
One of the best channel for learning google sheet.
YOU SIR, Are a Genius!!!!!
Thank You!
I've watched hours and hours of your videos. Thanks for sharing!
thanks, best video series on this topic so far, very helpful
Agreed !
Awesome.... very clearly presented.... had no idea this functionality was even available in Google Sheets.
Many Thanks
I really appreciate all of your videos. I learn lot many things from it. Wherever I stuck, I refer your videos. Please keep posting this kind of knowledge.
Thanks
If you want to watch more video about Google sheets, look on my channel STM Solution, i have many videos what you will like.
THANK YOUUUUUUUUUUUU THIS IS EXACTLY WHAT I WAS LOOKING FOR
Your videos are absolutely amazing. They have saved me so much time and aggravation because you explain it so clearly!
I completely agree. I'm so grateful for that guy. He made everything I was looking for easy to understand. Also, super creative in combining functions, scripts, & the likes.
Excellent Work. You made my system way better with this Tutorial. Best of Luck
I'm binging your vidoes right now. Very helpful and easy to understand!
:)
Always a great teacher!!
I want you to know that you are the best at what you do!
Your videos are great! I'm learning so much. Thanks a lot.
Thanks for the video. You saved me lots of trouble.
these explanations are very clear, thank you very much
This is the best video for Query Function. But it would be great if u provide the google sheet the same for practice.
I love your tutorial very much, you are so good at it, and always trying something no one else ever thought of. Very stimulating experience. Thank you
me too. Thanks U so much for sharing
Is it possible to create hyperlinks to the query results that takes me back to the original location of certain data.
I couldn't figure out why my reference dates were not pulling through correctly and I just forgot to add the parentheses 🤦🤦. You sir are a life saver🤗
Looking forward to part 2. Thank you.
I have multiple urls data in single cell and want to split the data into columns in google sheets.
As I have pasted from notepad to google sheets and it was pasted in Single cell and I want to see the data URLs in Columns wise as an Individual URls Please help me
how can i select mismatch between two columns? for example how do i get all data in column A that is not found in column B?
Thank you so much great video!
Excellent way to explain things. You made my day..Wish you the best.
Thank you so much... your videos are very helpful to me.. I learnt alot..
Good video! I usually open the same spreadsheet in two browser tabs as easier to switch between (shift-tab), rather than using Google sheets tabs.
Love your Content; You're just amazing
Keep up the good work
Крутое видео! Cool tutorial
CTRL SHIFT PgUp/PgDown to switch sheet tabs btw. Great video!
My keyboard doesn't have PgUp/PgDn :) Thanks!
@@ExcelGoogleSheets I use ALT up arrow to move back tap...
GREAT TUTORIAL! Super helpful! Easy to follow and is exactly what I was looking for.
Great to hear!
You used YEAR function to extract corresponding year from Column A. If we don't do that and manually type year as text, the query function recognizes the year data as text and we can use single ' ' to define the text after where statement? (5:40)
Good query explanation!
Hi sir can we put formula in sheet title? From cell to sheet title in order to edit easily the names of a plenty sheet at a time
May I know where to get the google sheets for me to practice along?
Thank you so much for the video. Its so useful to us.
so clear so net . thank you for this post
Thank You for Sharing...had quick question in my data one column has number format as "R1890" when I use query function it returns output as " " ,can you please help me how to resolve this issue.
Wow, this is game changing for me. Love the video. Thank you.
How do you do a query like you have shown here that puts data in all columns based on one column criteria (e.g. name)? For example the several people entered the data and you just want to pull all of their data into a new tab.
Thanks! Can you let me know if it's possible to write the query text that INCLUDES additional external cell references in the 'text' cell as you've demonstrated? I can't seem to get it to work - for example the follow query string returns an error, but works perfectly when pasted directly into the formula. Select * Where Col1=' "&C4&" '. Thanks! Very happy to pay for your advice as I can not find the answer to this ANYWHERE!
OF COURSE! Having just asked the question I've worked it out. ="Select * Where Col1='"&C4&"'" Thanks again FANTASTIC content.
Thank you, these videos are so useful! ❤️💖
Hi, Can you guide me to PLX queries? I recently joined one office where they are using plx queries, something similar to SQL query. Can you plz overview on this?
Hi, could you help me with this,
I have 4 columns with checkboxes "true or false values", and a columns with text,
I want to use Query to count true values on all 4 columns and group them in text column.
Awesome tutorial. Helped me understand Queries, thank you!
This is a great video but is there a copy of the data, so I can work upon it?
hey man, great stuff I'm trying to find an answer for and issue: how can you make updates on the referenced table (table with the query) without breaking it?
or different approach to make a separate column outside of the query, but to keep inline with the other rows. because currently when the query is updated the rows don't match on the new column that been made hope I'm clear cheers :)
Hi thanks for your video.
How i can use WHERE function to select multiple criteria to display the whole row data. Eg/ T32 or M33 is my primary key.
Please assist and thanks
+- 13:00 limit results to... Very helpful!!! Thanks
Thank you so much for detailed tutorial video. You saved my time. I will tweet about it
Awesome! what if there's a dropdown menu, how do we transfer the data to another tab based on the dropdown menu?
I don't understand.
All your videos are incredibly helpful, so thanks for all the work you put into it. However, regarding your examples of utilizing the QUERY function, I'm not sure how this approach is any different than using a Pivot Table. Or, perhaps that's not the point of this video, and you're simply showing people a different approach to filtering data. Either way, I found it insightful, empowering, and quite practical. Just wondering, though: Would there be limitations to accomplishing the same type/degree of filtering by using a Pivot Table instead? Keep up the outstanding work, and thank again!
I don't think it's the same. You can do Pivot Tables using QUERY function, but you can't do QUERY function with Pivot Table. That being said, if you were able to solve your problem with Pivot Table and it works, then you should keep using it.
Had a quick question on Query ....I have data in which one column has number format as " AB12234" when I use query function it's gives put as " " can you please provide resolution for this .... thank you
Can you create an example in-which the where clause is using a text value to search for. If I do this, I only get 1 match returned. But if I hard code the where clause i.e. where C = 'Test1' I get all the rows where C = Test1. If i use something like this where C = '&B3&' where B3 has the value Test1 I only get one row of data returned.
Hello there
I have formula in on column. and want to aplyy it to all other neighborhood columns. how i can do it?? by clicking and moving it to all othersdoesnt work because it changes cells in formula in wrong way
Excellent work, I really appreciate it
But how can I get this worksheet to practice?
Very good your explanation. Thanks.
But, and if I need include one calculated field like after select two fields (string, number) then I need calculate the % of each numbered register by sum of results? (select A,B where "one condition" and include field (as '%') using B/sum(B))
How can I do it?
thank you very much, I only have one inquiry can the query function be use when the data is continuously updating, I mean i have a data entry and it keeps populating everytime. thanks again
Yes you can Noli, but just keep the range open like if you are querying some range , for example instead of =QUERY(sheet1!A1:G40, use =QUERY(sheet1!A1:G So don't mention the row number and just keep the last column name. This will make your query function dynamic.
Thank you so much for the lesson
Maybe an idea for some future video:
So I have made a query formula like this
={QUERY('OLD application form Responses'!A1:M;"SELECT A,D,E,F,J WHERE B='Yes'";1);QUERY('Responses 1/2'!A1:T;"SELECT A,E,F,G,K WHERE B='Yes'";0)}
In order for my colleagues not to mess it up, I have protected cell with that formula with a password. But now there is a problem they can not filter the results. So I think a good solution would be for my formula to display results one row beneath so the cell protection would not affect their ability to filter. Any idea on how to solve this puzzle?
Thanks so much for your amazing videos. Am grateful Sir.
Can I get the file you used so i can also practice. Thanks
this is an amazing video I have learned a lot, Sir so could you allow me to use your Google sheet data I wanna practice it thanks
Hello
I have google sheet with some data in multiple column in my data there are some blank cell. Now I want to use query function for combine all column . When I am trying to use this function this also add blank cells. Please tell me how i can use this for filter blank..
If i make a color to particular cell, i need that color has to change/update on compiled sheet. Please guide
Sir i do have a question if i choose headers as 1 or -1 i get the same answer so what’s difference between that
ONE QUESTION
Along with =, >, LIKE, can 'not equal to' also be used?
I wish to have copy of the same data sheet for my practice . can I have the link so i can use n learn
Is there a way to label columns that are aggregated using "Label" in the query statement?
Thank you soooooo much, life saver :)
You referred to A1 which contains your query string. It seems that if you refer to a query string on another sheet it returns an error. I'm trying to replace the string in my formula with this and it fails: =concatenate("select A, B, C, D WHERE E = ","'",MainSheet!A7,"'").
Thanks a lot, very very helpful and comprehensive video for relevant topic
What he Sheets answer to Power Query?
Can you help me out
Do we have Query formula with Unique function
You are the best!!!
Is it possible to exclude few Columns ?
Something like for example
SELECT * EXCLUDE A,B WHERE B="Something"
This is a very helpful video indeed. Is there a limit with how many times I can use the command "AND"?
No doubt, the best videos on Google Spreadsheets!!!
Thanks for the material!
Is there any difference instead of using ORDER BY in the query function, use the SORT( query(...) ,1,true ) ?
Thanks!
Not Really, just an extra unnecessary step.
GREAT DESCRIPTIONS!
Hi, thanks for posting. Could you clarify something please? What is the difference between OR and AND . In this example I thought it would bring or CA or IL but brought both, like if it were an AND condition
The logic is applied for each row in your data, NOT applied to the column.
So AND means in the same row both conditions should be true.
if we put a query in another sheet, can we change the data in query sheet so we dont have to change data in data sheet? (in other words query and data can be added)
Excellent!
Is there a sheet that I can "Make a copy" of to work with while watching the QUERY videos?
If you want to watch more video about Google sheets, look on my channel STM Solution, i have many videos what you will like.
Your videos are amazing and they helped me a lot. Thank you so much!!!
Glad you like them!
I'm looking for guidance on how to pull out the following
I want to "select A,H,I,J,K,L,M,N,O where G contains 'HDW'" but I also want to SUM(M) and GROUP by L
If I write as follows
Select H,I,J,K,L, SUM(M) where G contains 'HDW' group by L,K,J,I,H
It doesn't do the group by correctly. I end up with duplicates of some of the items still. Any help would be great.
Thank you.
19:29 I relly need this
Hi Teacher! Do ypu have any video about selenium selector to scraping data through chrome using vba?
Not VBA, but I have JavaScript th-cam.com/play/PLRmEk9smitaXljDN2CjwZ96o4Aj87iuC5.html
Thanks for this video... VERY VERY VERY HELPFUL !!!
How to use query when the data is alphanumeric. I tried using query and importrange but would not recognize alphanumeric character
Another great video! Thank you
Thanks man!
Great tutorial. In this video, you have a 2nd spreadsheet called "Query Language Reference". How do you generate that file. It would be very handy to have that as a quick reference as you have done. Thanks.
Can query be used to find the last entry at the bottom of a list? I use a google sheet to track and update the cost of several items. My heading is in row 2. On row 3, I put the date in column A3 and the Price of item #1 in column B3, price of item #2 in column c3, etc. Id like to be able to put a function in B1 to take the most recent price of item #1 from the bottom of the list and always feed the last entry to the top of the sheet. Then I can use this cell with the current cost to send out for other formulas and calculations.
You can do this in several ways, for example, you can either sort the data as you feed it into query: e.g.
=query(sort(A3:C,1,false),"select A,B,C limit 1",0) -> that would sort the data so the most recent would be at the top (assuming data in column A, and the return only the top row with the "limit 1" part.
You can also sort using the query itself:
=query(A3:C,"select A,B,C where A is not null order by A desc limit 1",0)
Thank you, it make my work a lot easier
Is there anything spreadsheets cannot do with data? It's insane just how much there is to learn with these functions.
Very nice. Is there any way to dynamically select from all tabs? What I'm needing to do is select and SUM a single cell from each tab based on a date in another cell in the tab. Is that even possible?
It's definitely possible, but solution depends on specifics of your data. QUERY might not be the way to do it.
@@ExcelGoogleSheets - So, I have 'invoices' as separate tabs in a sheet. In a 'master sheet' I want to show the SUM of each month's invoices in a cell. So, 12 cells for 12 months in the master sheet that would have the sum/total of all invoices for that month. I keep adding tabs as I create invoices, so it needs to be dynamic.
Maybe this? th-cam.com/video/Vuh7lh2mhQk/w-d-xo.html
@@ExcelGoogleSheets Watching it now. Is there a way to use INDIRECT() in a query? I have a function that creates the 'string' of all the tabs. Trying to figure out how that could work and get the SUM of my E25 cell in each tab.
Amazing tutorial as always, this really helps with my new job so thanks a lot! Just a question, if I want to filter any filth month (e.g.: XX/05/XXXX, I tried the below (no space) but it doesn't work, do you have any idea? Thanks in advance!
SELECT A,B,AH
WHERE AH LIKE '_ _ _05 _ _ _ _ _ '
ORDER BY V DESC
LIMIT 10
LIKE only works on text columns, it won't work on dates.
Maybe try WHERE MONTH(AH) = 5
Can you please provide us with the material you work on?