Skip to main content
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 ...
Emilio Nicolás's user avatar
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}...
Rubén - Volunteer Moderator -'s user avatar
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 ...
doubleunary's user avatar
  • 14.5k
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 ...
marikamitsos's user avatar
  • 12.8k
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
marikamitsos's user avatar
  • 12.8k
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({...
ale's user avatar
  • 52.7k
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. ...
Rubén - Volunteer Moderator -'s user avatar
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.
CodeCamper's user avatar
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 ...
Tedinoz's user avatar
  • 5,701
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 ...
Felix Dombek's user avatar
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.+...
Erik Tyler's user avatar
  • 5,048
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 ...
Erik Tyler's user avatar
  • 5,048
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 ...
David Tan's user avatar
  • 985
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 ...
DraxDomax's user avatar
  • 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 ...
Martín's user avatar
  • 1,366
1 vote
Accepted

Sum column value if any of several criteria is met in the adjacent column

Please note the comments made directly to your original post, noting that you have spelling errors in both the formula and the Col-A data. However, assuming that your spelling were correct in Column A,...
Erik Tyler's user avatar
  • 5,048
1 vote
Accepted

Count instances of character in a column

EDIT (based on your comment) No mixed characters, it will always be a single character or a repeat of a single character For a draggable formula use =SUM(INDEX(IFERROR(SEARCH(B10,$B$2:$E$7)*LEN($B$2:...
marikamitsos's user avatar
  • 12.8k
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(&...
Rubén - Volunteer Moderator -'s user avatar
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)
marikamitsos's user avatar
  • 12.8k
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 ...
Erik Tyler's user avatar
  • 5,048
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 ...
marikamitsos's user avatar
  • 12.8k
1 vote

Use array as function parameters

Sheets' addition of the LET function enables storing intermediary calculation and addresses your need to limit repetition. LET QUERY(...) is a placeholder for your other formula. =LET(q,QUERY(...), ln,...
Blindspots's user avatar
  • 5,158
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-...
pjmg's user avatar
  • 2,000
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)
Gary's Student's user avatar
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&" '"&...
Rubén - Volunteer Moderator -'s user avatar
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)
Tedinoz's user avatar
  • 5,701
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 ...
Rubén - Volunteer Moderator -'s user avatar
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)),...
Oleg_S's user avatar
  • 791

Only top scored, non community-wiki answers of a minimum length are eligible