12

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row. My initial sheet looks like this:

enter image description here

I need to do the following to optimize this sheet:

  1. split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row.
  2. duplicate the data from all other cells on the original row (i.e. from columns A & C:E), so that each new row contains the full data for each business
  3. I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps

The sheet should then look like this:

enter image description here

Using =TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!

12

First of all, sorry for the late answer, but I have a solution for you to work with.

Code

function result(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][1].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == 1) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explained

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.
Add the script under Tools>Script editor and press the save button.

  • 2
    Awesome awesome awesome - buy this man a beer! Many thanks Jacob, that's exactly what I need. – kirk tab Jun 11 '14 at 22:49
  • 1
    And how to define the range? ( get this message "cannot read property "length" from undefined" Thanks! – user2060451 Jan 14 '17 at 12:16
  • on a really large file, this will likely fail...result too large. – Kyle Pennell Apr 30 at 0:57
3

A repeatable solution will need a script.

But for a one-time effort, you could just use =SPLIT(B3,CHAR(10)). This will give you all the person's names in side-by-side helper columns, like this:

enter image description here

Copy / Paste-special, values the helper-column contents.

And for each helper column that is used (hopefully not too many, 'cos hopefully you don't have too many people in any one business) manually copy-and-paste the block of rows to the end of the current block. (That's not a great description, but you get the drift.)

  • Hi Mary, thanks but I need the individ. person names dropped into new ROWS, (with extra rows auto-generated to accommodate them), then the surrounding info copied beside the new 'person' cells. And I need a 100% automated process - I can't do this manually for a thousand-or-so businesses! – kirk tab May 23 '14 at 8:22
  • You don't need to do the manual step for the whole thousand businesses. You only need to do it for the maximum number of pepole associated with any one of those businesses: eg if one business has six people, then there will be six new helper columns created, so you have to to a manual copy-and-paste six times, one for each column. – MaryC.fromNZ May 23 '14 at 10:08
  • 1
    Someone could write a script for this (I'm not that someone!) - but really the time spent testing it would probably be greater than the time spend doing it manually. – MaryC.fromNZ May 23 '14 at 10:09
2

For folks who might not immediately grasp how to use the helpful custom function in the accepted answer:

  • You need more than one sheet, in the example the two sheets are DATA and RESULT. The RESULT sheet is empty until the query has been run. You can see the query which refers to the DATA sheet in Jacob's screenshot.

  • You will most likely need to modify the comparison value for k on line 8 which refers to the column where your data to parse should be found. The same number will need to go into the 2nd array value on line 4.

  • You may need to modify the delimiter on line 4 which is currently \n

To make all this a teeny bit easier, I took the same code and extracted the delimiter and target column into variables set at the top of the function. As Jacob mentions target column count starts with 0 as the first number.

function result(range) {
  delimiter = ", "
  targetColumn = 10

  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][targetColumn].split(delimiter);    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == targetColumn) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

References

  • 1
    If I run the code, it says: TypeError: Cannot read property "length" from undefined. thank you so much. Answer should be here: Answer must be somewhere here (developers.google.com/apps-script/reference/spreadsheet/…) But I keep having the same error. – user2060451 Jan 14 '17 at 12:18
  • @user2060451 The solution on this answer shows the code of custom function, it's not intended to be ran from the script editor. I added a link to the custom function guide from Google. – Rubén Oct 16 at 16:11

protected by Community Sep 12 '16 at 12:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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