formulasgoogle sheetsgoogle-apps-script
As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") be modified if the dates are in descending order?
That is, Tony ate an apple on some day, how many days before did he eat a pear?
The following formula would yield the most elegant presentation.
=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")
This is a good alternative, but formula wise a bit different.
=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B
LABEL SUM(C) 'Costs'")
I've added the results into your dummy sheet.
The following little piece of code gives you the result, without prior knowledge about the character code number.
function mySplit(range, delimiter) {
var output = [], maxCol = range[0].length;
for(var i=0, iLen=range.length; i<iLen; i++) {
var colCounter=0, counter=0;
for(var j=0, jLen=range[0].length; j<jLen; j++) {
if(range[i][j] == "") {
colCounter++;
} else {
if(range[i][j].split(delimiter).length > 1) {
counter++;
}
}
}
output.push(counter + "/" + (maxCol - colCounter));
}
return output;
}
I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.
Best Answer
3 changes, those in red/bold :