Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

A lecturer from the European School of Physiotherapy in Amsterdam stumbled on a particular script I prepared, hoping it would resolve a problem he had.

After having had a little chit chat, it turned out that he wanted to have a class roster created, that needs to be exported into another program.

This is how the input sheet looks like (example file, Input sheet):

|     A    | ... |      G      |            H            |
|Opleiding | ... | Docent      | Klas                    |
|EPS       | ... | bakjj       | LP13-23                 |
|EPS       | ... | bakjj       | LP13-21;LP13-22;LP13-23 |
|...       | ... | ...         | ...                     |
|EPS       | ... | bakjj;elstm | LP13-40                 |

and his is how it needs to be (example file, Output 2 sheet):

|     A    | ... |      G      |            H            |
|Opleiding | ... | Docent      | Klas                    |
|EPS       | ... | bakjj       | LP13-23                 |
|EPS       | ... | bakjj       | LP13-21                 |
|          |     |             | LP13-22                 |
|          |     |             | LP13-23                 |
|...       | ... | ...         | ...                     |
|EPS       | ... | bakjj       | LP13-40                 |
|          |     | elstm       |                         |

How is that done?

See the first two sheets in the example file I prepared: ESP classes row split example

share|improve this question

1 Answer 1

up vote 0 down vote accepted

At first, I used plain old formula's to get the job done, see Output 2:

=JOIN(CHAR(10),SPLIT(Input!H3,";"))

Looking at the Output 2 sheet, it closely resembles the desired outcome. But, the outcome needs to have the empty lines in between !!

Then I revered to Google Apps Script:

function myOutput3(range) {
  // create array 
  var output = [];

  // add header to array
  output.push(range[0]);

  // itterate through rows
  for(var i=1, iLen=range.length; i<iLen; i++) {

    // capture split results
    var spl7 = range[i][7].split(";"), spl6 = range[i][6].split(";"); 
    var len7 = spl7.length, len6 = spl6.length;
    if(len7 == 1 && len6 == 1) {
      output.push(range[i]);
    } else {
      // determine which column has the highest length
      var tLen;     
      if(len7 >= len6) {
        tLen = len7;
      } else {
        tLen = len6;
      }
      // prepare output array      
      for(var j=0; j<tLen; j++) {
        if(j == 0) {
          var arr = range[i];
          arr[arr.indexOf(range[i][7])] = spl7[j];
          arr[arr.indexOf(range[i][6])] = spl6[j];
          output.push(arr);          
        } else {
          output.push([,,,,,,spl6[j],spl7[j]]);
        }
      }        
    }
  }  
  return output;  
}

This custom function will do the trick, without any API calls.
See example file I prepared: ESP classes row split example

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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