8
votes
Can you do a UNION SELECT in a Google Sheet?
It is possible to make a union in Google Spreadsheet very easily nowadays with this formula:
={'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A}
See more info in Google Docs Help: Using arrays in Google ...
- 275
7
votes
Accepted
Summing top three values in a column from a Google sheet
Formula
=ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0)))
Explanation
LARGE returns the nth greater element.
IFERROR if the first argument returns an error, returns the second argument.
{1,2,3}...
- 44.6k
4
votes
Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)
For folks who might not immediately grasp how to use the helpful custom function in the accepted answer:
You need more than one sheet, in the example the two sheets are DATA and RESULT. The RESULT ...
- 141
4
votes
Accepted
Google Sheets - Group/Aggregate data
You can remove sizes from names with regexextract() in cell F1 like this:
=arrayformula( iferror( regexextract(A1:A, "(.+?)-[\dA-Z]"), A1:A ) )
Then query the data:
=query(A1:F, "select ...
- 13.2k
3
votes
Accepted
How to use a query to find a row of same name but last entry, then copy a value next to it
Please use the following
(Adjust ranges to your needs)
=SORTN(QUERY({INDEX(ROW(D2:D)),D2:E},
"select Col2, Col3
where Col2 is not null
...
- 12.6k
3
votes
Summing top three values in a column from a Google sheet
You probably want to look at LARGE().
Returns the nth largest element from a data set, where n is user-defined.
The simplest, but by no means most elegant, solution I have is:
=if(iserror(large({...
- 52.3k
3
votes
Accepted
How to convert repeating rows into columns in Google Sheets
You can use the following query formula:
={{"Name";QUERY({B1:B},"where Col1 is not null and not Col1 contains '@'")},
{"Email";QUERY({B1:B},"where Col1 contains '@'")}}
Functions used:
QUERY
- 12.6k
3
votes
How do I filter the array returned by a filter or other formula?
Short answer
Assuming that each set of columns identified by its type will not have blank cells, a double QUERY and TRANSPOSE could be used to filter them:
=Transpose(
QUERY(
Transpose(...
- 44.6k
2
votes
Filter in Filter function Google sheets
The "elegant" way to use FILTER to filter A2:F where B2:B (Power) =I3 and D2:D (Manufacturer) =I2 is
=FILTER(A2:F,B2:B=I4,D2:D=I3)
The above works because filter allow multiple criteria arguments.
...
- 44.6k
2
votes
Accepted
Create list of cell addresses contained in a range
This one is fairly strait forward, please see the code below:
=textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4)))
You can change the last 4 to a 1 if you want absolute references.
- 827
2
votes
=IMPORTHTML forces content to date format
There are likely to be many solutions to your question. Even in this answer, I would expect that the structure could be simplified - possiblky along the lines of the answer by Rubén in Trying to use ...
- 5,520
2
votes
Accepted
How do you perform a bulk find & replace using a list of search phrases and their replacements?
You could try using a separate table (Sheet2) to do the replacing. Column A is the same as here (Sheet1), columns Sheet2!B:Z (or how many are maximally needed) are used to SPLIT the terms in Sheet1!B ...
- 166
2
votes
=IMPORTHTML forces content to date format
@John Tokish, this should do the trick:
=ArrayFormula(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings","table",1)),"200|20.+...
- 4,980
2
votes
How to have a query wrapped in an arrayformula auto-populate many rows
Place this formula in the top cell (i.e., header cell) of any otherwise empty column:
=ArrayFormula({"Header";IF(B2:B="",,COUNTIF(A2:A,B2:B))})
QUERY is not necessary, as you can ...
- 4,980
2
votes
Accepted
Regexextract to get a text in the middle of string
Using a capturing group, we can match a pattern then choose which part of the pattern we want to return.
=ArrayFormula(REGEXEXTRACT(A2:A4,"^\d\s+(\w\d+\s+-\s+[A-z 0-9]+)"))
Notes:
I'm ...
- 967
2
votes
Accepted
Parsing the result of an array formula inline?
Figured it out. Inline, it can look like this:
=INDEX(GOOGLEFINANCE(B2, "high", DATE(2022,7,26)), 2, 2)
The "2, 2" part means "2'nd row, 2'nd column" and that's how I ...
- 121
2
votes
How to QUERY - group SUMed results?
Maybe you can create a two-columns range with curly brackets, by stacking those two columns: {AG2:AH;AI2:AJ}
=QUERY({AG2:AH;AI2:AJ}; "select Col1, sum(Col2) where Col1 is not null group by Col1 ...
- 1,233
1
vote
Accepted
Problem with results of filter function from tabs that have no data yet
To avoid the issue with the FILTER function, use a query instead.
=QUERY({January!A2:O;
February!A2:O;
March!A2:O}, "where Col1 is not null",0)
- 12.6k
1
vote
Accepted
Sheets ArrayFormula with SUMIF
I added a new sheet ("Erik Help") with the following array formula in D2:
=ArrayFormula(IF(B2:B="",,SUMIF(IF(ROW(B2:B),ROW(B2:B)),">="&ROW(B2:B),C2:C)))
The ...
- 4,980
1
vote
Accepted
Using query function with ' where Col1 or Col2 is not null'?
EDIT
(following OP's comment)
So, how to say if either Col1 or Col2 is not null ?
" where Col1 is not null and Col2 is not null "
Original answer
Depending on your locale, the formula ...
- 12.6k
1
vote
=IMPORTHTML forces content to date format
Here is a solution using a single formula
=ArrayFormula(
REGEXREPLACE(SPLIT(TRANSPOSE(SPLIT(JOIN("",TRANSPOSE(QUERY(TRANSPOSE(
{IMPORTXML(A1,"//tr/*"),TRANSPOSE(SPLIT(REPT(REPT(&...
- 44.6k
1
vote
Google sheets Xlookup dont work but need results in table the max date on the condition of userlist
Problem 1 :
Page-2-Seznam članov > Coloumn C : It needs to be the max dates.
The function need to search on the list the name from Page-2-Seznam članov > Coloumn B and in area Page-3-...
- 1,968
1
vote
How to convert repeating rows into columns in Google Sheets
In C1 enter:
=index(B:B,2*row()-1)
and copy downward. In D1 enter:
=index(B:B,2*row())
and copy downward:
If you need to enter the formulas below row#1, replace:
row()
with:
rows($1:1)
- 191
1
vote
Accepted
Search a Google Spreadsheet and return results that are 'clickable' that takes you to the sheet and row
Instead of
=QUERY(Data1!A2:C, Data2!A2:C, Data3!A2:C "SELECT * WHERE A "&E3&" '"&F3&"'")
try
=QUERY({Data1!A2:C; Data2!A2:C; Data3!A2:C}, "SELECT * WHERE Col1 "&E3&" '"&...
- 44.6k
1
vote
I want to 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name
Try:
=SORT(UNIQUE(A3:B), 1, TRUE)
- 5,520
1
vote
Use a filter on an array of values
This is what is shown in the linked spreadsheet
Please note that each column includes a dropdown button. That is the way that Filter/Filter views work on Google Sheets.
If you want that the filter ...
- 44.6k
1
vote
Accepted
Is there a way to generate many rows and columns from a single value?
In Example there is formula
={TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(transpose(E1:E8)&",", $A$2))), ",")),
TRANSPOSE(SPLIT(REPT(JOIN(",",TRANSPOSE(SEQUENCE(A2,1,B2,1)))&",",COUNTA(E1:E8)),...
- 791
Only top scored, non community-wiki answers of a minimum length are eligible