Google Sheets duplicating rows by splitting comma separated multiple values

Question

I have Data in Google Sheets Where multiple Dates are mentioned in single column. I want to separate the Each date and same row should be repeated for each date.

I would appreciate your help.

Trying with this but its not working.

=ARRAYFORMULA({"DATE","EMPLOYEE NAME";QUERY(IFERROR(SPLIT(FLATTEN(IFERROR(Sheet1!A:D
&CHAR(9999)&SPLIT(Sheet1!H:H,","),)),CHAR(9999)),),
"SELECT Col2, Col1 WHERE Col2 IS NOT NULL ORDER BY Col2 ASC",0)})

Sheet Link

I tried with this but its not spliting the dates.

function result(range) {
  delimiter = ","
  targetColumn = 8

  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][targetColumn].split(delimiter);    
    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 == targetColumn) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(
 A2:A11&"×"&B2:B11&"×"&C2:C11&"×"&D2:D11&"×"&E2:E11&"×"&F2:F11&"×"&G2:G11&"×"&
 TRIM(SPLIT(H2:H11, ","))&"×"&I2:I11&"×"&J2:J11&"×"&K2:K11), "×", 0, 0), 
 "where Col8 is not null"))

---

update:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(FLATTEN(FLATTEN(
 A2:A11&"×"&B2:B11&"×"&C2:C11&"×"&D2:D11&"×"&E2:E11&"×"&F2:F11&"×"&G2:G11&"×"&
 TRIM(SPLIT(H2:H11, ",")))&"×"&TRANSPOSE(QUERY(FLATTEN(TRIM(SPLIT(I2:I11, ","))), 
 "where Col1 is not null", )))&"×"&TRANSPOSE(QUERY(FLATTEN(TRIM(SPLIT(J2:J11, ","))), 
 "where Col1 is not null", ))), "×", 0, 0), "where Col8 is not null", ))

or:

=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(FLATTEN(FLATTEN(FLATTEN(
 A2:A11&"×"&B2:B11&"×"&C2:C11&"×"&D2:D11&"×"&E2:E11&"×"&F2:F11&"×"&G2:G11&"×"&
 TRIM(SPLIT(H2:H11, ",")))&"×"&TRANSPOSE(QUERY(FLATTEN(TRIM(SPLIT(I2:I11, ","))), 
 "where Col1 is not null", )))&"×"&TRANSPOSE(QUERY(FLATTEN(TRIM(SPLIT(J2:J11, ","))), 
 "where Col1 is not null", ))), "×", 0, 0), "where Col8 is not null", )))