Google Sheets: Merge Non-Zero Values in Row with Column Header

concatenategoogle sheetsgoogle-apps-script

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.

Best Answer

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.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Google Sheets – Merging Column Headings Based on Specific Values

Try this formula for cell H2:

=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))

This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.

You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.

Best Answer

Code

Screenshot

Explained

Example

Related Solutions

Google Sheets – Make All Cells in Column Links from Values

Update

Code

Explained:

Example:

Google Sheets – Merging Column Headings Based on Specific Values

Related Topic