1

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: Attempt

=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

3
  • 1
    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?
    – Tedinoz
    Sep 10 at 2:40
  • Google Apps Script ≠ Spreadsheet formula. Google apps script is something just like a javascript.
    – Flyson
    Sep 10 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.
    – Tedinoz
    Sep 27 at 11:54

1 Answer 1

0

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

screenshot

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.