Google-sheets – Convert jagged array to 2D array in Google Sheets

google sheets

I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so each value is in its own cell, while retaining their associated IDs. This is illustrated in the attached image, and in this worksheet.

I'm looking for a script or formulas to perform this operation on an array with ~30,000 rows. My clumsy solution (below and in second tab of worksheet) gives an error above about 1000 rows, as the length of a string made by join() is limited to 50,000 characters:

Create a list of repeated ID's with the same number of elements as as the list of colors in column C

=rept(A4&",",COUNTA(split(B4,", ",false)))

Concatenate each column then split each column:

=transpose(split(join(",",C4:C),",",false))  
=transpose(split(join(", ",B4:B),", ",false))

Best Answer

For the OP example try the following formula:

=ArrayFormula(
IFERROR(
REGEXEXTRACT(","&
TRANSPOSE(TRIM(SPLIT(QUERY(TRANSPOSE(QUERY(
IFERROR(A4:A6&","&
REGEXEXTRACT(","&
SUBSTITUTE(B4:B6,", ",","),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")&";")
,,1000))
,,1000),";"))),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,2))-1)&",+([^,]+)"
)
)
)

For the 30,000 rows:

Change the references accordingly.
Replace 1000 by 30000 or a larger number.
- The 1000 is the headers argument of QUERY(data,query,[headers]). We want use a number big enough to include all the rows of the source data.
If the second column of the source data has rows with more than 6 items, replace the 6 in OFFSET(A1,,,1,6) by the corresponding number of items.

See the reference for further details.

References

JOINing code-points after CHAR conversion in Google Spreadsheets

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].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 == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

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.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Google-sheets – Split one column into two equal halves

Assuming the data is in column B, the following two formulas do the job. In cell C1:

=filter(B1:B, row(B1:B) < 1 + max(filter(row(B:B), len(B:B))) / 2)

In cell D1:

=filter(B1:B, row(B1:B) >= 1 + max(filter(row(B:B), len(B:B))) / 2)

Explanation:

Find the last row with data by max(filter(row(B:B), len(B:B)))
Divided by 2 and use as a threshold for splitting the column: rows above it go to column C, rows below it go to column D.