2

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this?

So for example I have this range:

Year | Month
2012 | 12
2013 | 01
2013 | 02
2013 | 03

And I want to be able to say: put that range under each other for X amount of times. For example 3 times:

Year | Month
2012 | 12
2013 | 01
2013 | 02
2013 | 03
2012 | 12
2013 | 01
2013 | 02
2013 | 03
2012 | 12
2013 | 01
2013 | 02
2013 | 03

How could I do this?

2

This little script will repeat any range (with or without the header) to the given amount:

function output(range,amount,header) {
  var output = [];

  // check if range is cell
  if(typeof range == 'string') {
    for(var i=0; i<amount; i++) {
      output.push([range]);
    }
    return output;
  } else {
    // check if header is wanted
    var value;
    if(header == 1) {
      output.push(range[0]);
      value=header;
    } else if(header == "") {
      value=0;
    } else {
      value=0;
    }  
    for(var i=0; i<amount; i++) {
      for(var j=value, jLen=range.length; j<jLen; j++) {
        output.push(range[j]);
      }
    }    
    return output;
  }  
}

In order to get the result from your question, use the following formula:
enter image description here

Under Tools>Script editor you can paste the code. Press the save button and you're on the go !!

I've prepared an example file for you to play around with: Repeat Range

1

You can rept range using formula (for e.g. you need rept range "C1:C4" 2 times):

=transpose(split(REPT(C1&","&C2&","&C3&","&C4&",";2);",";true))

  • You can link to the cell in another cell (for e.g. data!C1) , and for second column repeat formula with other column character. For x - push value for some cell (e.g. A1) and link on it. =transpose(split(REPT(data!C1&","&data!C2&",";data!A1);",";true)) – alunet Sep 13 '17 at 16:20
  • 1
    I'm inclined to leave this for now in the hope that it can be salvaged by editing. Not sure if that will happen, though. – jonsca Sep 13 '17 at 22:56
0

Just to demonstrate one way to achieve the result (not necessarily the means) I think OP wants. Import data with something like:

 =IMPORTRANGE(" k e y ","Sheet1!A1:B5")

Assuming Year in A1 of Sheet1 and into A1, then in A6 and copied across to B6:

 =A2

with the pair copied down to suit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.