Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets

Question

I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of the picture links below.

Pictured here is the data set I'm working with: Data Set

This is what I'd like the output to be: Expected Result

And this is result of my attempt using the formula listed below: enter image description here

=arrayformula(query(split(flatten(A3:A6&"|"&SPLIT(B3:B6,",")&"|"&SPLIT(C3:C6,",")&"|"&SPLIT(D3:D6,",")),"|"),"Select * where Col1 is not null"))

Let me know if additional sub tables would be easier to migrate them together as well. I've used the following links to get me part of the way there, but could use some help on finishing it up.

How to transpose & split multiple columns and repeat specific cells in a column

Split and repeat without

TheMaster · Answer

You can use loops to achieve this using a custom function:

/**
 * Splits cells by commas and makes expanded arrays
 * Converts https://i.stack.imgur.com/hS7Zo.jpg to
 *     https://i.stack.imgur.com/61AK7.jpg
 * @customfunction
 * @OnlyCurrentDoc
 * @author TheMaster
 * @link https://stackoverflow.com/a/73670957
 * @param {A3:D6} values A 2D array
 */
const splitFlattenYadaYada = (values) => {
  const out = [];
  for (const row of values) {
    const splitRow = row.map((e) => String(e).split(','));
    const allDone = Array(row.length - 1).fill(0);
    for (
      let i = 0, j = 0, currOut = [row[0]];
      !allDone.every(Boolean) || (out.pop(), 0);
      i = 0, currOut = [row[0]], j++
    ) {
      while (++i < splitRow.length)
        currOut.push(splitRow[i][j] ?? ((allDone[i - 1] = 1), ''));
      out.push(currOut);
    }
  }
  return out;
};

/**/console.config({maximize:true,timeStamps:false,autoScroll:false});/**/ 
const values = [
    ['A1', '123,456', '166,134,141,121', '1,2,3,1'],
    ['A2', '123', '145', '2'],
    ['A3', '111,241', '1,2,3', '1,1,1'],
  ],
  out = [];
for (const row of values) {
  const splitRow = row.map((e) => String(e).split(','));
  const allDone = Array(row.length - 1).fill(0);
  for (
    let i = 0, j = 0, currOut = [row[0]];
    !allDone.every(Boolean) || (out.pop(), 0);
    i = 0, currOut = [row[0]], j++
  ) {
    while (++i < splitRow.length)
      currOut.push(splitRow[i][j] ?? ((allDone[i - 1] = 1), ''));
    out.push(currOut);
  }
}
console.table(out);

Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets

1 Answers