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.
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.
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?
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
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.
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...."
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...
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
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.
