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:

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

And this is result of my attempt using the formula listed below:

=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

Answer 1

Welcome!

Let's try a not-so-long script instead of a long formula:

function SplitAndCombine(aSourceData,delimiter =",") {
  var temp = [];
  var ind = 0;
  for (let i = 0; i < aSourceData.length; i++) {
    var result = [Array(aSourceData[i].length)];
    result[0][0] = aSourceData[i][0];
    for (let j = 1; j < aSourceData[i].length; j++) {
      var splitted = String(aSourceData[i][j]).split(delimiter);
      for (let k = result.length; k<splitted.length; k++) { 
        result[k] = [Array(aSourceData[i].length)]
        result[k][0] = aSourceData[i][0];
      }
      for (let k = 0; k < splitted.length; k++) {
        result[k][j]= String(splitted[k]).trim();
      }
    }
    for (let k = 0; k < result.length; k++) {
      result[k][0]=aSourceData[i][0];
    }
    for (let j = 0; j < result.length; j++) {
        temp[ind++] = result[j];
    }
  }
  return temp;
}

Choose Extensions - Apps Scripts, paste this code there and save.

Return to your spreadsheet and type to A12 =SplitAndCombine(A3:D8)

---

OUTPUT

By the way, today is Programmers Day (256th day of the year) - congratulations to everyone involved