Thanks a lot man, I spent 4 hours yesterday trying to figure out how to query/filter based on values of a list and that dynamic thing you built there was perfect. If I could like this video 10000000 times I would.
Interesting approach, thanks for sharing it! There's a simpler way, however. The next function will do the same: =FILTER(Transactions!$A$1:$L, MATCH(Transactions!$A$1:$D, A$2:$A$9,0))
Thank you for your great videos, they are the very best available. I like using query in place of formula drive functions, but wonder if one way is better as far as processing speed. Can I go too far with queries, or is it a case by case kind of thing? Thanks again.
Amazing tutorial. Thank you a lot. You are really tacking a lot o important issues for full functionality of google query. Please keep up! May i ask a question? Since google query lacks FROM clause. Can you think of any other way to tackle it besides joining tables with match/index or Vlookup?
Exactly what I was looking for. One question though. You mentioned in the very last minute if I use numbers instead of text I need to remove the a apostrophes - I kind of struggle with it. Can you put your final formula without those?
how would I go about adding another argument. for example Where Col2 / B "Kyle Cruz" I'm wanting to importrange where everything is relevant to the query but ignore data with certain criteria.
I mean if your "Transaction" sheet would be in another workbook...Would it be something like this?: =IMORTRANGE(QUERY(Transactions!$A$1:$L$20001, ”SELECT A, B, C, D, F WHERE D = ' " & TEXTJOIN(" ' OR D = ' " , TRUE,A2:A9)&” ’ ”,1))
That is the spirit! However, you will need to use "SELECT Col1, Col2, Col3, Col4, Col6 WHERE Col4 = ..." instead of letters. (it is a must to type Col1, not col1 or COL1, k?) Moreover, the IMPORTRANGE functions has 2 arguments: URL and range. So it will be more like: =QUERY(IMPORTRANGE("[your spreadsheet URL here]","[the range you want to import]";"SELECT...."
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 the reverse be done? Is it possible to say “where not D =“ or “D ” in order pull data but exclude ones that match a particular column? I just tried it and it’s not working for me for some reason
@@ExcelGoogleSheets Didn't work for some reason. The only thing that worked was "where not D matches '" & TEXTJOIN("' AND NOT D matches '",TRUE,K3:K)&"'" Even with my formula, if I switch the "AND" to "OR" makes my formula not work. Do you know why that might be?
matches means you use regular expression, so there might be spaces before and after in your data, also QUERY is by default case sensitive, so if you type APPLE it will not match Apple.
how can i troubleshoot if this doesnt work anymore on my file? the other day it reflects normally but suddenly today it doesnt work as intended, i tried changing it to an array as well but nothing happened
Does this still work in sheets? I can't get this to cooperate for the life of me. Values will return only if copied to multiple cells and they don't seem to be updating when source data is changed.
Hi ! Thanks for the tutorial its very usefull. But (always there is a "but") i have problem How can i make a join 3 tables like: ..........Table PERSONS................... IdPerson,name,age 1,Tom,15 2,Nicolas,20 3,John,22 .........Table PROFESSIONS............ IdJob,jobName,hoursPerWeek 20,tech,6 21,teacher,4 22,poet,10 ......PERSONS IN PROFESSIONS... IdPerson, IdJob 1,20 1,21 2,22 2,20 3,22 ......................................................... And i need to see all the people's work(and other colums) like: name,jobName Tom,Tech Tom,Teacher Nicolas,Poet Nicolas,Tech John,Poet Can you help me ? Thanks for your time
I wonder if you can help me with the following "challenge" Col1 is Status Col2 is Value Col3 is a url I want to parse out values from the url in Col3 that contains utm parameters. The url looks like this: xyz.com.au/?Google&NSW&Air&gclid=EAIaIQobChMIqqWxqs_J2QIVxhWPCh2iQgFLEAAYASAAEgKcHvD_BwE There are a number of known variables for: source medium campaign content I want parse out the 4 values from the url in Col3 and use those values to create a pivot table with the values from Col1 and Col2. In other words, starting with 3 columns I want to end with: Col1 Status Col2 Value Col3 source Col4 medium Col5 campaign Col6 content Hopefully that makes sense?
Hello Sir, I'm tired resolving this when I'm retrieving the data from another sheet. The list is displayed like this: M. Dedeepya Pinki Kumari Kalyani Sheena Evelyn Mula Aarthi Pandre Maheshwari Mustafa Ahmed Srinath Lekkala Phanindra Babu Akhil Dulam I should get the names as a list one below the other. Like this M. Dedeepya Pinki Kumari Kalyani Sheena Evelyn Mula Aarthi Pandre Maheshwari Mustafa Ahmed Srinath Lekkala Phanindra Babu Akhil Dulam Could you please help me out...
Thanks a lot man, I spent 4 hours yesterday trying to figure out how to query/filter based on values of a list and that dynamic thing you built there was perfect. If I could like this video 10000000 times I would.
Thank you, you made me realize that there are so many ways to cheat in google sheets. A real eye opener!
Interesting approach, thanks for sharing it! There's a simpler way, however. The next function will do the same: =FILTER(Transactions!$A$1:$L, MATCH(Transactions!$A$1:$D, A$2:$A$9,0))
I have that in FILTER video, but this is QUERY series.
Thanks a million!! This knowledge never gets old.
Like always, right on the money! Just what I was needing.
Thank you for your great videos, they are the very best available. I like using query in place of formula drive functions, but wonder if one way is better as far as processing speed. Can I go too far with queries, or is it a case by case kind of thing? Thanks again.
Interesting idea, well explained - can't imagine ever using it though!
As with any other function, it's useful when you need it :)
God bless your family
Amazing tutorial. Thank you a lot. You are really tacking a lot o important issues for full functionality of google query. Please keep up! May i ask a question? Since google query lacks FROM clause. Can you think of any other way to tackle it besides joining tables with match/index or Vlookup?
At this point there is no JOIN like SQL, so we'll have to stick with regular lookups for this.
Fantastic job
Exactly what I was looking for. One question though. You mentioned in the very last minute if I use numbers instead of text I need to remove the a apostrophes - I kind of struggle with it. Can you put your final formula without those?
This is what I want . THANKS
how would I go about adding another argument. for example Where Col2 / B "Kyle Cruz"
I'm wanting to importrange where everything is relevant to the query but ignore data with certain criteria.
Great tutorial! Thanks! How about extracting the same data, but from another worksheet? How to do it?
Not exactly sure what you mean, but probably IMPORTRANGE function is what you need. I have a video on it.
I mean if your "Transaction" sheet would be in another workbook...Would it be something like this?: =IMORTRANGE(QUERY(Transactions!$A$1:$L$20001, ”SELECT A, B, C, D, F WHERE D = ' " & TEXTJOIN(" ' OR D = ' " , TRUE,A2:A9)&” ’ ”,1))
That is the spirit! However, you will need to use "SELECT Col1, Col2, Col3, Col4, Col6 WHERE Col4 = ..." instead of letters. (it is a must to type Col1, not col1 or COL1, k?)
Moreover, the IMPORTRANGE functions has 2 arguments: URL and range. So it will be more like:
=QUERY(IMPORTRANGE("[your spreadsheet URL here]","[the range you want to import]";"SELECT...."
+Leonardo Polon Thanks!
Can I use join syntax in google sheet sir. Not the combination of query & vlookup.
No, query function doesn't support joins. At least not at the moment.
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
Hello, It's a great video. I need this formula but with opposite, so I need data what it are not in the list. Can you help me?
I found a solution I use AND operator in TEXTJOIN instead of OR...
thank you
🙏🏻
Can the reverse be done? Is it possible to say “where not D =“ or “D ” in order pull data but exclude ones that match a particular column? I just tried it and it’s not working for me for some reason
WHERE D != 'something' or WHERE D 'something' both should work.
@@ExcelGoogleSheets Didn't work for some reason. The only thing that worked was "where not D matches '" & TEXTJOIN("' AND NOT D matches '",TRUE,K3:K)&"'" Even with my formula, if I switch the "AND" to "OR" makes my formula not work. Do you know why that might be?
matches means you use regular expression, so there might be spaces before and after in your data, also QUERY is by default case sensitive, so if you type APPLE it will not match Apple.
how can i troubleshoot if this doesnt work anymore on my file?
the other day it reflects normally but suddenly today it doesnt work as intended, i tried changing it to an array as well but nothing happened
Does this still work in sheets? I can't get this to cooperate for the life of me. Values will return only if copied to multiple cells and they don't seem to be updating when source data is changed.
Yes, it does.
#cheeky
Hi !
Thanks for the tutorial its very usefull. But (always there is a "but") i have problem
How can i make a join 3 tables like:
..........Table PERSONS...................
IdPerson,name,age
1,Tom,15
2,Nicolas,20
3,John,22
.........Table PROFESSIONS............
IdJob,jobName,hoursPerWeek
20,tech,6
21,teacher,4
22,poet,10
......PERSONS IN PROFESSIONS...
IdPerson, IdJob
1,20
1,21
2,22
2,20
3,22
.........................................................
And i need to see all the people's work(and other colums) like:
name,jobName
Tom,Tech
Tom,Teacher
Nicolas,Poet
Nicolas,Tech
John,Poet
Can you help me ?
Thanks for your time
At this point you will need to write a script for this. It's not going to be simple.
@@ExcelGoogleSheets :( Ok, i will look for a solution.it has to be done.
Anyway, thanks for your help !!
I wonder if you can help me with the following "challenge"
Col1 is Status
Col2 is Value
Col3 is a url
I want to parse out values from the url in Col3 that contains utm parameters.
The url looks like this:
xyz.com.au/?Google&NSW&Air&gclid=EAIaIQobChMIqqWxqs_J2QIVxhWPCh2iQgFLEAAYASAAEgKcHvD_BwE
There are a number of known variables for:
source
medium
campaign
content
I want parse out the 4 values from the url in Col3 and use those values to create a pivot table with the values from Col1 and Col2.
In other words, starting with 3 columns I want to end with:
Col1 Status
Col2 Value
Col3 source
Col4 medium
Col5 campaign
Col6 content
Hopefully that makes sense?
I would you regexextract function for that. I have several videos on Sheets regex functions.
dude if you did this video with a growly voice you would sound like strong bad. :)
Hello Sir, I'm tired resolving this when I'm retrieving the data from another sheet. The list is displayed like this:
M. Dedeepya Pinki Kumari Kalyani Sheena Evelyn Mula Aarthi Pandre Maheshwari
Mustafa Ahmed
Srinath Lekkala
Phanindra Babu
Akhil Dulam
I should get the names as a list one below the other. Like this
M. Dedeepya
Pinki
Kumari
Kalyani
Sheena Evelyn Mula
Aarthi Pandre
Maheshwari
Mustafa Ahmed
Srinath Lekkala
Phanindra Babu
Akhil Dulam
Could you please help me out...