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?

• Did my solution work for you? Jul 22 '13 at 20:21

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;
output.push(range);
} 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: 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

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))` Sep 13 '17 at 16:20
• 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. Sep 13 '17 at 22:56

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.