New answers tagged google-sheets-query
0
It is not clear if you want to pull data from another tab of the document you are working on or you want to pull data from another tab of the "url of my document" and don't want to type the whole thing again.
Pulling info from the same document but different tab using query would be as easy as replacing importrange("url of my document"; "INPUT!A1:F28") ...
1
Here's a Query that can do the trick :
=QUERY(Data!B1:D,"SELECT B,AVG(C),AVG(D) GROUP BY B ")
You can use label to rename the column's header.
0
Two way to do this with a Query.
With a regular expression and matches :
=query('Raw Data'!C1:J1000,
"Select E, D, I, J, C, F, H
where H contains 'Monday'
and F matches 'Red|Green|Blue'
and C='schools'
order by E")
matches - A (preg) regular expression match. haystack matches needle is true if the regular expression in needle matches ...
0
Here is my solution:
if you have a column called "city" create a new cell somewhere with the index of that cell:
A B C ... Y Z
--------- ------------ ------------ ------ --------------- ---------------
Name Phone City ... Column Name Column Index
Vidar ...
1
In this case I believe the FILTER function would be a much better fit than the QUERY (which becomes quite convoluted).
Please try the following formula:
=FILTER(A1:I,A1:I1="Adam")
Pro Tip
Two significant differences of FILTER vs QUERY is that using the FILTER function you get to "transfer/preserve" both the numbers' original formatting as well as ...
0
Disclaimer : This is maybe not the most efficient way to do it, but more something like an intuition on how to do it.
Last try :
=textjoin(",",true,
ArrayFormula(char(64+unique(transpose((split(textjoin(",",true,
ArrayFormula(if(REGEXMATCH(A1:Z100,"Adam"),COLUMN(A1:Z100),))),",")))
))
))
Use this formula in your query like this :
=query( range , "Select ...
1
You can use your original QUERY function.
You just have to change like to matches along with the TEXTJOIN function.
It is matches that allows you to use a regular expression (regex) in the QUERY function.
=QUERY(B14:B,"select B where B matches '.*["& TEXTJOIN(,true,A14:A16) &"].*'")
UPDATE
following OP's comment:
...make words which only ...
2
Determine column alphabet letter as follows: =char(64+MATCH(N,1:1,0)) where you substitute the number you want for N=4,5...10.
Then add that to the query string formulaically. Done:
=QUERY(A1:D4,"select "&char(64+MATCH(5,1:1,0))&" where A = 'Speed'")
Important limitation for generalisations: this only works for column index up to 27, because it ...
1
You can use the filter function with REGEXMATCH as a parameter, try this in C1 :
=FILTER(B2:B100,arrayformula(REGEXMATCH(B2:B100,TEXTJOIN("|",true,A2:A4))))
It filter the range B2:B100 based on which result returns the regex
Explanation :
The following formula returns False or True based on the content in the cells B2:B100. The operator | is basically a ...
0
Use REGEXMATCH :
=countif(ArrayFormula(regexmatch(A1:A10,"^Bud Light$")),true)
This formula counts the cells who match the regex ^Bud Light$ : Match exactly Bud Light
You can add lower() and proper()to clean the range you want to test.
1
It was a syntax issue, my data range was incorrect.
Top 50 recent answers are included
