# Tag Info

12

First of all, sorry for the late answer, but I have a solution for you to work with. Code function result(range) { var output2 = []; for(var i=0, iLen=range.length; i<iLen; i++) { var s = range[i].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.

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

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

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

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

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

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

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

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

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<>"";...

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