3

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

Shijilal
  • 2,175
  • 10
  • 32
  • 55
  • Your spreadsheet can't be accessed. Please fix permissions. Do you want to be able to write in the empty cells that are added? – JPV Oct 24 '18 at 09:40
  • @jpv The permission issue solved. Updated link. What i need is 2 empty cell after 3 cells of data. No need to write in those empty cells. – Shijilal Oct 24 '18 at 10:02

2 Answers2

2

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)),"")))
Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • @i-i But if a blank cell is in data itself,then its removed from the result. I need that blank cell too there in the result. How to get that? – Shijilal Oct 24 '18 at 11:23
  • Read syntax of `textjoin`. Try Changing true to false – TheMaster Oct 24 '18 at 11:25
  • @i-i No its not having any effect..check this sample sheet. https://docs.google.com/spreadsheets/d/11nMvUWn3xcTfxlk4v30KruPr03HSheMk1jrxZPpJ_p4/edit?usp=sharing – Shijilal Oct 24 '18 at 11:30
  • @Shiji `But if a blank cell is in data itself,then its removed from the result. I need that blank cell too there in the result.` Your expected result doesn't contain that blank cell. It's completely removed. This will keep the blank cell `=ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN(" ",0,IF(MOD(ROW(A2:A16),3)=1,A2:A16&REPT(" ",2),A2:A16)),"")))` But your expected result changes the question by a huge margin- almost a different question requiring a completely different answer. – TheMaster Oct 24 '18 at 11:39
  • @i-i sorry about that. I forgot to change the expected result. Let me check the latest solution – Shijilal Oct 24 '18 at 11:46
  • @i-i its giving different result with same data starting at different rows. Please do check the sheet 2 in the same sample sheet – Shijilal Oct 24 '18 at 13:33
  • @Shiji Please take time to understand how the script works. What I gave you was clearly marked as "sample". It's not intended to solve all 100 variations of the issue. It's a working sample for the screenshot in your question. You should take a few days to learn and modify it to your liking. For eg, `MOD` uses `ROW` numbers to separate and add rows. If G2 had to have the same result as F2, Try G2: `=ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN(" ",0,IF(MOD(ROW(A2:A199),12)=1,B3:B200&REPT(" ",10),B3:B200)),"")))` You can also do this by changing `1` or `12` or plenty of other ways. – TheMaster Oct 24 '18 at 14:11
0

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?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • How to modify it,if i want 12 blank cells in between? – Shijilal Oct 24 '18 at 12:27
  • Change the second parameter to 12: =rowsBetween(A2:A18, 12,) – JPV Oct 24 '18 at 15:39
  • sorry,i meant to ask..what to do if i need data in 12 cells and after that gap – Shijilal Oct 24 '18 at 16:11
  • I updated the code. Now you can enter the rows with data (before the gap) in the third paramater. See E2 in your spreadsheet for an example. – JPV Oct 24 '18 at 16:21
  • Hi, I tried this formula but it gives me and error message... " TypeError: Cannot read property 'forEach' of undefined", do I need to change anything in the code? Also when I add the "=rowsBetween(A2:A16, 2, 12,)" nothing happens, did you get this to work? – Mee Feb 06 '22 at 10:34