# How can I make this custom formula work with multiple columns?

I came across this script that is a custom formula:

``````function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split("\n");
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 == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
``````

The script/custom formuled worked like a charm for one column, but I couldn't get this to work with multiple columns.

I tried something that seems correct to me but the script didin't work.

``````    function result(range) {
var splitCol = 17;
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split(",");
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 == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
var splitCol2 = 18;
var output3 = [];
for(var ii=0, iiLen=range.length; ii<iiLen; ii++) {
var ss = range[ii][splitCol2].split(",");
for(var jj=0, jjLen=s.length; jj<jjLen; jj++) {
var output4 = [];
for(var kk=0, kkLen=range[0].length; kk<kkLen; kk++) {
if(kk == splitCol2) {
output4.push(ss[jj]);
} else {
output4.push(range[ii][kk]);
}
}
output3.push(output4);
}
}
var outputs = output2, output3;
return outputs;
}
``````

Sample data

• Welcome to Web Applications. Code should be added as text not as image. Also please add all the required details to make it easier for others to reproduce the problem. In cases like this one, please include some sample data and the formula that you tried as well the any error message on the Google Sheets user interface as well on the scripts executions page. Commented Sep 20, 2021 at 17:56
• @Rubén sorry about the mess, I edited the whole question. Commented Sep 20, 2021 at 18:17
• Hi and welcome. Would you please clarify... Your code comes from this webapps question but it (and your code) deals with values separated by line breaks. You said "The script/custom formuled worked like a charm" yet your data is separated by commas, so how could it work for you? I also tried it with your data and it does NOT work. Commented Sep 28, 2021 at 7:38

I was interested to know whether your outcome could be achieved with a formula (yes, I do understand that the result would be dynamic not static, but it was an interesting problem.

Try this formula:

`=query({ARRAYFORMULA(query(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(B2:B, ","))<>"", "♦"&row(A2:A)&"♠"&A2:A&"♠"&SPLIT(B2:B, ",")&"♠"&"♠", )) ,,999^99)),,999^99), "♦")), "♠",true,false)),"select Col1, Col2, Col3, Col4,Col5"));ARRAYFORMULA(query(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(C2:C, ","))<>"", "♦"&row(A2:A)&"♠"&A2:A&"♠"&"♠"&SPLIT(C2:C, ",")&"♠"&"♠", )) ,,999^99)),,999^99), "♦")), "♠",true,false)),"select Col1, Col2, Col3, Col4,Col5"));ARRAYFORMULA(query(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(D2:D, ","))<>"", "♦"&row(A2:A)&"♠"&A2:A&"♠"&"♠"&"♠"&SPLIT(D2:D, ","), )) ,,999^99)),,999^99), "♦")), "♠",true,false)),"select Col1, Col2, Col3,Col4,Col5"))},"Select Col2, Col3, Col4, Col5 where Col2 <>'' order by Col1")`

Logic

The formula consists of three nested queries.

• the first deals with csv in Column B
• the second deals with csv in Column C
• the third deals with csv in Column D

Each query include place keepers that result in four columns of data.

The output sequence is managed by tracking `row(A2:A)` and sorting by that output Column.

SAMPLE

Hat tip: @player0 ref