CODEWITHSUNDEEP
google-sheetsarray-formulas
Ogggrrr
Ogggrrr

Reputation: 113

Google Sheets: Repeat a row based on COUNTA of a cell, ARRAYFORMULA

I have a table as below. The table is updated from time to time so the exact number of rows is not know:

+--+-------+-------------+
|a |red    |1, 1, 1,     |
+--+-------+-------------+
|b |green  |2, 2,        |
+--+-------+-------------+
|c |blue   |3,           |
+--+-------+-------------+

I need to repeat each row based on the COUNTA in the Column 3 as follows:

+--+-------+-------------+
|a |red    |1            |
+--+-------+-------------+
|a |red    |1            |
+--+-------+-------------+
|a |red    |1            |
+--+-------+-------------+
|b |green  |2            |
+--+-------+-------------+
|b |green  |2            |
+--+-------+-------------+
|c |blue   |3            |
+--+-------+-------------+

I wrote a formula but to ensure it addresses enough rows I have to manually add another row to that formula (consider the columns are E, F, and G):

={
if(len(E2)>0,{
transpose(split(rept(E2&"****",COUNTA(split(G2,", "))),"****")),transpose(split(rept(F2&"****",COUNTA(split(G2,", "))),"****")),TRANSPOSE(split(G2,", "))}
,{"","",""});


if(len(E3)>0,{
transpose(split(rept(E3&"****",COUNTA(split(G3,", "))),"****")),transpose(split(rept(F3&"****",COUNTA(split(G3,", "))),"****")),TRANSPOSE(split(G3,", "))}
,{"","",""});

if(len(E4)>0,{
transpose(split(rept(E4&"****",COUNTA(split(G4,", "))),"****")),transpose(split(rept(F4&"****",COUNTA(split(G4,", "))),"****")),TRANSPOSE(split(G4,", "))}
,{"","",""});

if(len(E5)>0,{
transpose(split(rept(E5&"****",COUNTA(split(G5,", "))),"****")),transpose(split(rept(F5&"****",COUNTA(split(G5,", "))),"****")),TRANSPOSE(split(G5,", "))}
,{"","",""})

}

etc.

Example sheet.

Sine the exact number of rows is not known I would like to convert this into an ARRAYFORMULA for rows 2-1000.

Would that be possible at all? If yes, what would be the formula? Thanks!

Upvotes: 1

Views: 701

Answers (1)

JPV
JPV

Reputation: 27262

Paste this script in the script editor.

    /** 
 * Splits the array by commas in the column with given index, by given delimiter
 * @param {A2:B20}  range Range reference
 * @param {2}  colToSplit Column index
 * @param {","}  delimiter Character by which to split
 * @customfunction
 */


function advancedSplit(range, colToSplit, delimiter) {
    var resArr = [], row;
    range.forEach(function (r) {
        r[colToSplit-1].replace(/(?:\r\n|\r|\n)(\d|\w)/g,", ").split(delimiter)
            .forEach(function (s) {
                row = [];
                r.forEach(function (c, k) {               
                   row.push( (k === colToSplit-1) ? s.trim() : c);
                })
                resArr.push(row);
            })
    })
     return resArr.filter(function (r) {
        return r.toString()
            .replace(/,/g, "")
    })
    }

Then in the spreadsheet use this script as a custom formula

=advancedSplit(E2:G, 3, ",")

I hope this helps?

Upvotes: 1

Related Questions