# Tag Info

15

Use a custom function. Code function result(range) { var splitCol = 1; // split on column B var output2 = []; for(var i=0, iLen=range.length; i<iLen; i++) { var s = range[i][splitCol].split("\n"); for(var j=0, jLen=s.length; j<jLen; j++) { var output1 = []; for(var k=0, kLen=range.length; k<kLen; k++) { ...

4

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} is an array of literals. See Using arrays in Google Sheets. ArrayFormula makes that functions like LARGE and IFERROR return an array of values.

4

An ARRAYFORMULA would cover that. You can reference a range of columns like so: =ARRAYFORMULA(Sheet1!A:H) Complete columns are taken in to account, thus if something gets inserted on sheet 1, it will be included in sheet 2.

3

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

3

Short answer Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter. Long answer An alternative to ARRAYFORMULA is the use of embedded arrays also named inline arrays literal arrays array parameters "your own arrays" Example of embedded array of constants ={"A","B";1,2} ={A1:B2;D1:E2;G1:H2} Example of embedded array of ...

3

A repeatable solution will need a script. But for a one-time effort, you could just use =SPLIT(B3,CHAR(10)). This will give you all the person's names in side-by-side helper columns, like this: Copy / Paste-special, values the helper-column contents. And for each helper column that is used (hopefully not too many, 'cos hopefully you don't have too ...

3

This formula in essence will do that. Formula =ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, "")) Explained The IF statement, in combination with the ARRAYFORMULA, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing. Note Now that you have the solution you want, you're not ...

3

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( QUERY( E1:M13, "Select * where M = '"&F15&"'", 1 ) ), ...

3

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({data},1)),0,large({data},1))+ if(iserror(large({data},2)),0,large({data},2))+ if(iserror(large({data},3)),0,large({data},3)) (carriage returns added for ...

3

2

Short Answer Google Sheets is a very powerful tool that have several alternatives to achieve a result similar to UNION-SELECT. Alternatives Embedded arrays An alternative to use SPLIT-TRANSPOSE-JOIN-CHAR way is to use embedded arrays. One advantage of this method is that it doesn't require to construct/deconstruct strings, so the limit of the number of ...

2

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 sheet is empty until the query has been run. You can see the query which refers to the DATA sheet in Jacob's screenshot. You will most likely need to modify the ...

2

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.

2

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. Reference FILTER

1

To use SumIf you need a single column with data to be summed up, and a second column with something you can check (a boolean for example). I extract every 2nd cell for column 1 : ={ filter(transpose(C4:AG4); mod(transpose(ArrayFormula(COLUMN((C4:AG4))+1));2)=0); filter(transpose(C5:AG5); mod(transpose(ArrayFormula(COLUMN((C5:AG5))+1));2)=0); filter(...

1

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(CONCATENATE( REPT({wi!B2:B&"♦"&wi!C2:C}&"♥", wi!A2:A)), "♥")), "♦"))

1

Another way to do this is to use a filter function inside another filter function. For example, if your data is in B2:E5, with headers in row B, you can use: =filter(b2:e2 & filter(B2:E5,B2:B5 = "Martin"),(B7:E7 = "b") + (B7:E7="d")) This will filter by name, and then by your column headers "b" and "d".

1

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

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&" '"&F3&"'") The above because QUERY doesn't allow multiple ranges but it could use an array.

1

Try: =SORT(UNIQUE(A3:B), 1, TRUE)

1

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 shows all the values in the array, you should change the shape of the array from 3 columns 1 row to 1 column 3 rows as follows ={"Picard";"Q";"Data"} If you ...

1

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)),","))} First part of formula generates repeating list of clients, where E1:E8 - initial range of clients, \$A\$2 - cell with number of repeats you need =...

1

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 formula. Assuming "My Goal" is in cell C1 replace it with the following formula: ={"My Goal";arrayformula(if(A2:A="","",500))} You can also replace 500 with a ...

1

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") Also added order by Col1 to sort by date.

1

I've been working on your file, even before the bounty period, and it as been a struggle. I wanted to give you the automation te ARRAYFORMULA can give in combination with a bit of Google Apps Script. In doing so, I wanted to understand the calculations a bit and thus I started with optimizing the first formula for column E: =ARRAYFORMULA(IF(A4:A<>"";...

1

Possible but may require some 'compromising': =arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1)) in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required. in say G1 and copied down ...

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