How can I split 2 columns of cells with multiple lines into unique rows, keeping/duplicating the surrounding data?

Question

I'm working with a sheet that has 2 columns with cells that may have multiple rows in the cell. I'm trying to figure out how to split both cells at the same time (as they both should have the same number of lines in them) and maintain/duplicate the surrounding data for each new split. I've seen a few different ways to accomplish this using both a formula and a script, seen below, but those all seem to hinge on only splitting a single column of cells, not 2 columns.

My data set is below and it's pulling information from a Forms submission, so I'm not able to change how the data is input, I just need to try and figure out the best way to parse the data. Ideally, both cells will have the same lines and will be split on a carriage return (I'm most familiar with CHAR(10) being used) and I just need to figure out splitting both simultaneously. I put together some example data in the sheet linked below. I've also included one example of code that I've found that performs this task on a single column of cells (from here, but is quite old -- I'm also not quite sure how to modify my range to not be column B in the example)

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

Data sample available here:
https://docs.google.com/spreadsheets/d/1edR-pKxx98l01zFE2LBUkWvQJPKQ9IV_LIHieDwf-1I/edit?usp=sharing

Any help would be greatly appreciated!

Answer 1

• You want to achieve to convert from RAW DATA to DESIRED RESULT in your shared Spreadsheet.
• You want to achieve this using the custom function.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample script:

When your shared Spreadsheet is used, please put the formula of =result(A3:E6) to a cell.

function result(values) {
  return values.reduce(function(ar, e) {
    var [colD, colE] = e.slice(-2).map(function(f) {
      return f.split("\n").map(function(g) {return g.split(/\s+/)[1] || g});
    });
    return ar.concat(colD.map(function(f, j) {return e.slice(0, 3).concat([f, colE[j]])}));
  }, []);
}

• At first, the values of column "D" and "E" are splitted.
• As the next step, each row is created from the splitted values.

Result:

Note:

• This sample script can be used for your sample Spreadsheet. If the format of the values are changed, the script might not be able to be used. Please be careful this.

References:

• reduce()
• map()
• split()
• concat()

If I misunderstood your question and this was not the result you want, I apologize.

Added:

From your 2nd shared Spreadsheet, I prepared the script for converting from RAW DATA to DESIRED RESULT. I thought that the column "D" is different from 1st shared Spreadsheet in your 2nd shared Spreadsheet. In this case, "1 [email protected]\n2 [email protected]\n3 [email protected]" is required to be converted to Bill, Steve and Ted. But this cannot be done. So I prepared the following sample script using your 1st logic. It's like [email protected], [email protected] and [email protected].

Sample script:

In order to use this script, please put =result(A3:O6) to a cell.

function result(values) {
  return values.reduce(function(ar, e) {
    var head = e.splice(0, 3);
    var [colD, colE] = e.splice(0, 2).map(function(f) {return f.split("\n").map(function(g) {return g.split(/\s+/)[1] || g})});
    var values = colD.map(function(f, j) {return head.concat([f, colE[j]]).concat(e)});
    return ar.concat(values);
  }, []);
}

Answer 2

Try this:

function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet227');
  var osh=ss.getSheetByName('Sheet222');
  osh.clearContents();
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var vA=rg.getValues();
  var n=0;
  for(var i=0;i+n<vA.length;i++) {
    var dA=vA[i+n][3].split('\n');
    var eA=vA[i+n][4].split('\n');
    if(dA.length>1) {
      for(var j=0;j<dA.length;j++)  {
        var nr=[vA[i+n][0],vA[i+n][1],vA[i+n][2],dA[j],eA[j]];
        vA.splice(i+n++,0,nr);
      }
      vA.splice(i+n--,1);
    }
  }
  vA.unshift(hA);
  osh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
}

Answer 3

I think I answered this on Reddit?

https://reddit.app.link/uGOAxYRcg3

Matt

Answer 4

With formula:

={ arrayformula(
   vlookup(
     transpose(
       split(
         textjoin(
           ",",true,
           filter(regexreplace(row(D3:D) & regexreplace(D3:D,"[^\" & char(10) &
                  "]",""),"[" & char(10) & "]","," & 
                  row(D3:D)),D3:D<>""
           )
         ),",",true,false
       )
     ),{row(A3:A),A3:D},{2,3,4},false)
   ),
   transpose(
     split(
       textjoin(
         "," ,true,
         filter(
           regexreplace( D3:D,"[\" & CHAR(10) & "]","," ),D3:D<>""
         )
       ),",",true,false
     )
   ),
   transpose(
    split(
      textjoin(
        "," ,true,
        filter(
          regexreplace( E3:E,"[\" & CHAR(10) & "]","," )
          ,E3:E<>""
        )
      ),",",true,false
    )
  )
}