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

CodePudding user response：

I would like an all-in-one solution but, so far, the only way I got this to work was by using 3 sheets:

• source
• helper sheet (where I use the script that splits the column where there may be multiple values)
• sheet where values are being queried and joined

If nothing better, at least it works :)

CodePudding user response：

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.