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

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

• Hi, welcome to WebApps. 1) Would you please provide a written description of the algorithm for the proposed formula. 2) Would you please ensure that you explain why the "Group ID" is repeated for "A202212" but why "A202213" includes two blank "Group ID" cells - this seems to be inconsistent. 3) You've also tagged "apps script" - why did you do that? Sep 10, 2022 at 2:40
• Google Apps Script ≠ Spreadsheet formula. Google apps script is something just like a javascript. Sep 10, 2022 at 18:35
• "Google Apps Script ≠ Spreadsheet formula". I agree; and your questions says "I am looking for a formula", and your example is a `QUERY`. So... why did you tag "Apps-Script"? And would you please answer the questions in my comment above. Regardless of how one might construct an answer, the information in those questions is important. Sep 27, 2022 at 11:54

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

• The OP's "Expected Results" include a Group ID for both instances of "A202212". Sep 27, 2022 at 12:06