New answers tagged google-sheets-query
1
There is no simple built-in way like a single function formula or tool included in the Google Sheets UI menus to do what you are asking.
One way to proceed, among many, is by using SUBTOTAL functions but you should add an additional row or use the Feature / subfeatures rows and on each of them add the corresponding ranges manually to each of them.
Another ...
1
Combine the xml data by using an array
Enter this formula in Cell A4
={IMPORTXML($B$1,$B$2);IMPORTXML($D$1,$D$2)}
Then transpose every 14th set of rows using this formula
=ArrayFormula(split(transpose(split(regexreplace(query(array_constrain(if(mod(row(A4:A), 14) = 4, "^", "|") & A4:A,max(row(A4:A) * (A4:A <> "")),1),...
0
Instead of where Col1='Adonist'",0) use where Col1='"&P37&"'",0).
The above because the QUERY function second parameter is a TEXT value so to add a cell value as part of a condition, this cell value can be inserted by splitting the second parameter and concatenating the parts. In this case & used for the concatenation.
0
If you have a date in A2 then
=text(A2,"ddd")
would give "Mon", "Tue", "Wed", etc.
=text(A2,"dddd")
would give "Monday", "Tuesday", "Wednesday", etc.
0
Use filter() with match(), like this:
=filter( 'Sheet1'!A1:IM, match('Sheet1'!A1:A, 'Sheet2'!A1:A15, 0) )
0
Put this formula in the first row of a free column:
=arrayformula( query(
{
flatten( iferror(
vlookup( BD!A1:A; BD!A1:A; sign(sequence(1; columns(BD!D1:H))); false )
) )
\
flatten(BD!D1:H)
};
"where Col1 is not null
and Col2 is not null
order by Col1";
1
) )
Your sample spreadsheet's locale is ...
0
Try authorizing the importrange(). In the spreadsheet where you have the issue, put this in a free cell:
=importrange("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU", "A1")
Chances are that the new formula cell will display a red corner flag. Click it to authorize the import. After that, your existing formulas that use importrange() should ...
0
Try this:
=UNIQUE(FILTER(BQ4:BQ10,ISERROR(VLOOKUP(1&BQ4:BQ10,BP4:BP10&BQ4:BQ10,1,FALSE))))
In plain English, this read as follows:
"Stick a 1 to the front of each element of the BQ range and try to find that string among any strings made up of the corresponding BP and BQ values for any row in the range. If found, filter out the BQ value. If not ...
0
Try this formula:
=query({A1:B8},"select Col2 where Col1="&arrayformula(textjoin(" or Col1=",true,transpose(query({query({A1:B8}, "select Col1, count(Col2) where NOT (Col1 = 1 OR Col1=2 or Col1=3) and (Col1=4 or Col1=5 or Col1=6) group by Col1 label count(Col2) '' ")},"select Col1 where Col2 = 1"))))&"&...
Top 50 recent answers are included