Thank you for posting this video. It showed me how I can use one large master spreadsheet with lots of data and then use Query to create more detailed analytical views. Before I was creating a sheet for each type of analysis I wanted to conduct which was not efficient. So, thank you!
Nice to watch a video where the language is nice and clear and crisp. I've only been dabbling with this since Christmas :D so I am stuck. I built a simple database (by watching various videos from different people), for my movies and tv series. I made another sheet with a search box 'C3' and if I write 'Open' in it then using this formula =QUERY(Data!A1:K, "SELECT * WHERE LOWER(B) = LOWER('"&C3&"') OR LOWER(F) = LOWER('"&C3&"') OR LOWER(G) = LOWER('"&C3&"') OR LOWER(I) = LOWER('"&C3&"') OR LOWER(K) = LOWER('"&C3&"')", 1) any movie or TV show with the word Open appears, in my case "Open All Hours", the problem comes when I try searching for 24, I get an error because is numbers not letters how do I rectify this
Thank you! I've had a problem with getting Google Form data updating my calculations page automatically and this gave me the ability to grab and calculate responses automatically! Woot woot and subscribed!
Eye-opening results with Query()! Your videos are excellent. We do a lot of energy data analysis, and your course on this and other functions will shorten and make our work easier.
This is a great resource!! I love receiving your weekly tips as well!I learned so much- just wish there was a way to ask a few specific questions about my own data.
What would the query look like if you wanted to show each country and the sum of the heights for all buildings in that country? I'm thinking of something similar to examine a list of SKUs and the number of items sold. Looking to get a unique list of SKUs and the total sold for that sku. This seems similar, but I'm not seeing how to tie the two together...
Twitter brought me here coz I wanna learn google sheet. I'm in-charge of making reports but I never used google sheet. They told me to start with import range. Maybe you can give me list that would perfectly match with import range for my reporting.
what would be the best way of manipulating cell data after a QUERY? I ran into an issue when querying data that had been manipulated to be a HYPERLINK via the ARRAYFORMULA and it didn't bring the link over
Thinking out loud, i should probably QUERY the data first and somehow "duplicate" or "clone" that and apply arrayformulas but still not sure if i can do that
Hi, great video ! I have a question for You - is there a way to not only order and count by country but to all data that segregated - query a full list with buildings name and a country it belongs (6:46) + build year is > 2010? To be precise: Country_Name | How_many_Buildings Country_1 | 12 Country_2 | 5 Country_3 | 7 and Country_Name | Building_Name | Year > 2010 Country_1 | some_name_1 | 2012 Country_1 | some_name_2 | 2010 Country_2 | some_name_3 | 2015 Country_2 | some_name_4 | 2016 Country_2 | some_name_5 | 2011 Country_3 | some_name_6 | 2010 Country_3 | some_name_7 | 2010 Country_3 | some_name_8 | 2019 I want to do this by using query - is there a way?
You have to put the cell reference outside the quotes e.g. "select * where B = '"&G1&"'" You also have to have single quotes before and after the cell reference, but they need to be inside the double quotes.
Great content, although the audio could be better. Specifically the voiceover is distant and hollow. Mic was likely placed too far from the speaker's mouth in a room which was probably small with reflective surfaces. I'm picking at nits.
I'm a self/google taught excel nerd and it's videos like this that help me expand my horizons. Thanks for sharing!
Thank you for posting this video. It showed me how I can use one large master spreadsheet with lots of data and then use Query to create more detailed analytical views. Before I was creating a sheet for each type of analysis I wanted to conduct which was not efficient. So, thank you!
Great overview of how the Query function works. It's great how similar it is to SQL.
Nice to watch a video where the language is nice and clear and crisp. I've only been dabbling with this since Christmas :D so I am stuck. I built a simple database (by watching various videos from different people), for my movies and tv series. I made another sheet with a search box 'C3' and if I write 'Open' in it then using this formula =QUERY(Data!A1:K, "SELECT * WHERE LOWER(B) = LOWER('"&C3&"') OR LOWER(F) = LOWER('"&C3&"') OR LOWER(G) = LOWER('"&C3&"') OR LOWER(I) = LOWER('"&C3&"') OR LOWER(K) = LOWER('"&C3&"')", 1) any movie or TV show with the word Open appears, in my case "Open All Hours", the problem comes when I try searching for 24, I get an error because is numbers not letters how do I rectify this
Thank you! I've had a problem with getting Google Form data updating my calculations page automatically and this gave me the ability to grab and calculate responses automatically! Woot woot and subscribed!
Thank you! This was so helpful and clear.
Eye-opening results with Query()! Your videos are excellent. We do a lot of energy data analysis, and your course on this and other functions will shorten and make our work easier.
Thanks, man!!! After watching many videos I found your video and finally, I got the result. Thanks from Bangladesh. And I subscribed you.
I'm a Query Beginner so thank you for posting. Helped a lot
Great little lesson - very understandable for beginners, unlike many others on this topic!!
This is a great resource!! I love receiving your weekly tips as well!I learned so much- just wish there was a way to ask a few specific questions about my own data.
You're a life-saver! Thank you for posting this!
Thank you so much ! Everything's so clear !
Really liked it! Just subscribed.
didnt know sheets was so interesting, complex and useful, thank for the video.
Excellent video - thanks!
Luckily I found the right channel!
is it possible to order by a column and if 2 values are the same go by a second column?
Terrific video, thank you!
Really well done. Thank you so much.
What would the query look like if you wanted to show each country and the sum of the heights for all buildings in that country? I'm thinking of something similar to examine a list of SKUs and the number of items sold. Looking to get a unique list of SKUs and the total sold for that sku. This seems similar, but I'm not seeing how to tie the two together...
Twitter brought me here coz I wanna learn google sheet.
I'm in-charge of making reports but I never used google sheet. They told me to start with import range. Maybe you can give me list that would perfectly match with import range for my reporting.
Great video, I did not know about this function in google spreadsheets
Outstanding! Thank you!
This is great! How would I query the data in a separate sheet of the same file?
what would be the best way of manipulating cell data after a QUERY? I ran into an issue when querying data that had been manipulated to be a HYPERLINK via the ARRAYFORMULA and it didn't bring the link over
Thinking out loud, i should probably QUERY the data first and somehow "duplicate" or "clone" that and apply arrayformulas but still not sure if i can do that
Thank you for posting! Very useful!
Here's a query, why does it sound like you recorded this is an echo chamber?
Hi! is there a way to use a cell reference (eg. B2) with WHERE rather than text? I can't get it to work
where you selected country could you use this formula to look at a separate cell where you can type the country you can to sort buy?
Yes, you can use something like:
...Where B ends with '"&A62&"' And C = '"&B52&"'
Thanks for the video, my question is how do you not return the headers?
Wow this is amazing. Thanks a lot.
Awesome! I love Spreads!
Why did google sheets is suggesting me other formula?
QUERY(A2:E6; "select avg(A) pivot B"; -1)
no commas here, and it doesn't works anyway
just great!!!!
If in data one column have formula how to get result in query function
Hi, great video !
I have a question for You - is there a way to not only order and count by country but to all data that segregated - query a full list with buildings name and a country it belongs (6:46) + build year is > 2010? To be precise:
Country_Name | How_many_Buildings
Country_1 | 12
Country_2 | 5
Country_3 | 7
and
Country_Name | Building_Name | Year > 2010
Country_1 | some_name_1 | 2012
Country_1 | some_name_2 | 2010
Country_2 | some_name_3 | 2015
Country_2 | some_name_4 | 2016
Country_2 | some_name_5 | 2011
Country_3 | some_name_6 | 2010
Country_3 | some_name_7 | 2010
Country_3 | some_name_8 | 2019
I want to do this by using query - is there a way?
I have one doubt If give range (ex. >100 to
🤯
Thank you sir!!
Hi Ben, can google sheet generate payslip that can be emailed automatically to recipients email?
well done thanks so much
Is it possible to use a named range in the query? Something like =query(buildings, "select "Country" .... )
Sure. Example =query(k,"select *"), k is Named range
THANK YOU SO MUCH
Great. (y)
How do you use the value in a cell as one of the conditions? that is, if I want to say, SELECT * WHERE B = somethingiputinG1
You have to put the cell reference outside the quotes e.g. "select * where B = '"&G1&"'"
You also have to have single quotes before and after the cell reference, but they need to be inside the double quotes.
@@benlcollins Thanks! I was using numbers (IDs) so no single quotes are needed.
good
I’m not gonna watch this because either you or RUclips made it not able to be full screen on an iPad.
Great content, although the audio could be better. Specifically the voiceover is distant and hollow. Mic was likely placed too far from the speaker's mouth in a room which was probably small with reflective surfaces. I'm picking at nits.
lose the annoying jingly music, for the love of god