Split cell values into new rows with common values

Question

CONTEXT

I have a source table with multiple columns (Source B table in example spreadsheet).

On column E we have max one name and on column F we can have from 0 to multiple names separated by a comma.

AIM

When there are values in F, add one row per each name (E,F - the last one can have more than one separated by a comma) and duplicate the common values.

It should keep the rows where there are no values in F.

The final result will have one less column than Source table.

WHAT I'VE TRIED

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(IF(ISBLANK(E3:F6);; 
 A3:A6&"♦"&B3:B6&"♦"&C3:C6&"♦"&D3:E6&"♦"&E3:E6&"♦"&G3:G6&"♦"&H3:H6&"♦"&I3:I6&"♦"&J3:J6&"♦"&K3:K6&"♦"&L3:L6)); "♦")); 
 "where Col3 <> 0"; 0))

Problem

This formula was applied to Source A table (in the example spreadsheet and that's = Source B table but without comma separated values in F), which didn't have more than one value in F at the time, and:

duplicates the common values as expected but it's not showing the values from F. Just duplicates the ones from E.
ads blank row if the source row doesn't have values in F
because in column K only one row has a value, it messes up the final data
doesn't do anything different with or without comma separated values in F

Example spreadsheet

--- EDIT ---

I've found this other post with a script that I also tested on the example sheet. I've reduced the number of columns to ease the test and because I think it's aiming for the last column being the one that is supposed to be splitted, if applicable.

It does split but it repeats the "Main" client on the left column and the splited one on the right, where I would like the outcome to have all clients on the same column.

user_17926478 · Answer

To give you an idea, you can try the following script, I used the "Source for Script" Sheet and created another sheet called "Result". As you can see in the script, I used arrays to collect the data, then added it to the "Result" Sheet.

function splitNewRow() {
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source for Script");
  var data = source.getRange(2, 1, source.getLastRow(), source.getLastColumn()).getValues();
  var outcomeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Result");
  newArray = [];
  newRow = [];

  for (i=0; i < data.length; i++){
    var claim = source.getRange(i+2,1).getValue();
    var mainClient = source.getRange(i+2,2).getValue();
    var others = source.getRange(i+2, 3).getValue();
    
    hasComma = others.indexOf(",") != -1;
    
    if (others != "" && hasComma == true){
      newArray = [];
      newArray = others.split(",");
      newArray.push(mainClient);

      for (j=0; j < newArray.length; j++){
        newRow = [claim, newArray[j]];
        outcomeSheet.appendRow(newRow);
      }
    }

    else if(others != "" && hasComma == false){
      newRow = [claim, mainClient];
      outcomeSheet.appendRow(newRow);
      
      newRow = [claim, others];
      outcomeSheet.appendRow(newRow);
    }

    else{
      newRow = [claim, mainClient];
      outcomeSheet.appendRow(newRow);
    }
  }
}

After running the script, you get the following:

If you have any questions, let me know.

Split cell values into new rows with common values

1 Answers