Tag Info

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 ...
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}...
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 ...
• 15.3k
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 ...
• 13k
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
• 13k

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.8k

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. ...
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.
• 867

=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,811
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

=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.+...
• 5,088

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 ...
• 5,088
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 ...
• 1,070
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

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,416
1 vote
Accepted

Split text in Google Sheets with a delimiter which contains spaces

There are many ways to achieve the result you are looking. Here is one of the oldies, using RIGHT, LEN and FIND =ArrayFormula(RIGHT(A:A,LEN(A:A) - FIND(" on ",A:A) - 3) Another way, in this ...
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,...
• 5,088
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:...
• 13k
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)
• 13k
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 ...
• 5,088
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 ...
• 13k
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(&...
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,...
• 7,080
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)
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&" '"&...
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,811
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 ...
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)),...
• 781
1 vote
Accepted

Add single value as constant line to diagram

The only option in charts is to use one of the many trendline options. Since your column is just a single arbitrary value and you want to plot it the best thing you can do is put it all in one ...
• 867
1 vote

Converting multiple columns to list

The solution was that I didn't need to unpivot, just reorder. =query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null order by Col1"...

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