Repeat range of items multiple times in Google Sheets

Question

I want to repeat the range of items multiple times (Value provided).

e.g.

I have this in a sheet1!A

Detroit
Texas
Utah
California 

Now I want to repeat them 3 times to get the output at Sheet2!A like:

Detroit
Texas
Utah
California 
Detroit
Texas
Utah
California 
Detroit
Texas
Utah
California 

What should be the formula?

I got this formula:

https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/

But it's not working in Google Sheets

Answer 1

You can create vertical arrays using array literals {;}. You can automate this process by creating a loop using REDUCE.

=LAMBDA(rg_to_repeat,times,
  REDUCE(
    rg_to_repeat,
    SEQUENCE(times-1),
    LAMBDA(a,c,{a;rg_to_repeat})
  )
)(A1:A4,4)

• First, we need to know the number of times we need to iterate/repeat. Excluding the first one, we have times-1.
• Then we use SEQUENCE to create a array sequence of numbers from 1 to 3(if times is 4)
• For each number in the array, REDUCE, calls the provided LAMBDA function with the initial value first and then the previous accumulator and the current value, i.e., the number 1(2 and 3 in next iterations)
• For each iteration, the function joins the previous accumulator with the range to repeat: {a;rg_to_repeat}

Advantage:

• Works even with 2D arrays.
• No string manipulation.
• Auto filling array formula.

Disadvantages:

• Newer functions have artificial limits on iteration and memory usage

Answer 2

this will work only if you paste it into A5 of the same sheet and drag it down:

=IF(ISBLANK(INDIRECT(ROW(A1))),INDIRECT((ROWS($A$1:A4)-(COUNTA(A:A)-2))),A1)

otherwise, you can use:

=QUERY({Sheet1!A1:A4;Sheet1!A1:A4;Sheet1!A1:A4},"select *",0)

or:

=TRANSPOSE(SPLIT(REPT(JOIN(",",Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4&","),3),",",1))

or:

=TRANSPOSE(SPLIT(REPT(Sheet1!A1&","&Sheet1!A2&","&Sheet1!A3&","&Sheet1!A4&",",3),",",1))

or:

function REPEAT(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;
  }  
}

---

=REPEAT(Sheet1!A1:A4,3,0)