# Tag Info

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}...

### 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 ...
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 ...
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 ...

### 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({...
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

### 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(...

### 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.

### =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 ...
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 ...

### =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.+...

### 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 ...
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 ...
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 ...

### 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 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)
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 ...
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 ...
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

### 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 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)
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)),...

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