formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query
I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B – AC). I'm trying to find the first month that they visited the business and the last month we've seen them.
I have attached a screenshot and I have also published a small section of the sheet.
I have been struggling with this for a week and have gotten nowhere. I would greatly appreciate any help.
With the following piece of code you can prepare the summary as you want.
function mySummary(header, range) {
var output1 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var output2 = [];
for(var j=0, jLen=range[0].length; j<jLen; j++) {
var value = range[i][j];
if(value != "" || value > 0) {
output2.push(value + " " + header[0][j]);
}
}
output1.push([output2.sort().join(", ")]);
}
return output1;
}
The custom function needs two parameters to work with: header & range. The first iteration (var i=0) handles the rows and the second (var j=0) handles the columns. Each cell will be evaluated for empty ("") or non-zero (> 0) values. When either of them is true, the result is pushed into an intermediate array (output2). When the first row is completed, the combined result is sorted and joined and pushed into a new output array (output1), before starting with the second row.
I've created an example file for you: Special Summary
Add the script under Tools>Script editor and press the save button.
Best Answer