CODEWITHSUNDEEP
google-sheetsgoogle-sheets-querygoogle-sheets-formula
Shijilal
Shijilal

Reputation: 2169

Adding blank rows in a set of data in Google Sheets

I have a set of data. What i am looking forwards is to add 2 blank rows after each set of 3 values like this

enter image description here

Hope to get help in getting this solved.

you can find the sample google sheet here : https://docs.google.com/spreadsheets/d/11nMvUWn3xcTfxlk4v30KruPr03HSheMk1jrxZPpJ_p4/edit?usp=sharing

Thanks

Shijilal

Upvotes: 3

Views: 3457

Answers (2)

JPV
JPV

Reputation: 27262

Some time ago, I created this custom function that may help you. I changed it slightly to meet your requirement and added it to the script editor.

function rowsBetween(range, s, rowsWithData, text) {
var n = [],
    a = [],
    i = 0;

while (i < s) {
    a.push(text
    )
    i++;
}
range.forEach(function(r, i) {

    n.push(r);
     if((i + 2) % rowsWithData == 1) {
    a.forEach(function(x) {
        n.push(x);
    });
    }
});
return n;
}

This script will allow you to enter in the spreadsheet this (custom) formula (see also cell E2)

=rowsBetween(A2:A16, 2, 12,)

See if that works for you?

Upvotes: 0

TheMaster
TheMaster

Reputation: 50555

Solution:

  • IF it's the third row, Add 3 bunnies separated by a space, else keep the values as it is
  • JOIN them all and SPLIT by a bunny and TRANSPOSE

Sample:

=ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN("🐇",1,IF(MOD(ROW(A2:A16),3)=1,A2:A16&REPT("🐇 ",3),A2:A16)),"🐇")))

Upvotes: 2

Related Questions