1

I hope this example is clear. I have played with join and filter but can't get the Summary field to display as below:

Example

Row 1 is a list of subjects. Row 2 is a teacher's subject allocation (calculated from a lookup). I want the Summary cell to simply work through each row, if there is a non-zero value in a cell, take that number and concatenate to the subject, then move on to the end of the list.

Happy to try a script.

0

With the following piece of code you can prepare the summary as you want.

Code

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

Screenshot

enter image description here

Explained

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.

Example

I've created an example file for you: Special Summary
Add the script under Tools>Script editor and press the save button.

1

With the following formula you can do it as well.

Formula

=JOIN(
   ", ",                                   ==> delimiter join
   TRANSPOSE(
     SORT(
       TRANSPOSE(                          ==> data sort  
         SPLIT(
           JOIN(                           ==> data for split
             ",",                          ==> delimiter join
             ARRAYFORMULA(
               IF(
                 A2:D2<>"",                ==> if statement
                 A2:D2 & " " & $A$1:$D$1,  ==> true statement 
                 ""                        ==> false statement
               )
             )
           ),
           ","                             ==> delimiter split
         )
       ),
       1,                                  ==> column index sort
       TRUE                                ==> ascending or descending
     )
   )
 )


 copy/paste
 =JOIN(", ",TRANSPOSE(SORT(TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(IF(A2:D2<>"",A2:D2 & " " & $A$1:$D$1, ""))),",")),1,TRUE)))

Screenshot

enter image description here

Example

I've created an example file for you: Special Summary

Note

The formula is quite long I would say. Adding other criteria will make it even longer. The advantage is the quick re-calculation of the result. A dis-advantage, despite the ARRAYFORMULA, is that it isn't possible to address the range as in the script version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.