CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formularepeat
geekyfreaky
geekyfreaky

Reputation: 69

Repeat range of items multiple times in Google Sheets

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

Upvotes: 4

Views: 5401

Answers (5)

TheMaster
TheMaster

Reputation: 50445

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

Upvotes: 3

M. Hoffman
M. Hoffman

Reputation: 66

Google sheets keeps evolving...

Here's a formula that stacks / repeats a 4-row x 3-col array of cells to create a 48-row x 3-col array.

The KPI_LEVELS is a named range referencing the upper left corner of a 4-row x 3-col data table in another sheet. The OFFSET / MOD 4 makes the array repeat the 4-row source array to reach the desired numbers of rows, 48 in this case:

=MAKEARRAY(48,3, LAMBDA (r,c, OFFSET(KPI_LEVELS, MOD(r-1,4), c)))

Upvotes: 0

pnuts
pnuts

Reputation: 59450

You might copy down from Row1:

=offset(Sheet1!A$1,mod(row()-1,4),)

for as many sets of four as suits you.

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

I might as well add this as an answer:

=IF(ISBLANK(INDIRECT("Cities!A"&ROW(A2))),INDIRECT("Repeat!A"&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2)

works fine in Google Sheets and Excel.

This also works and may be preferable in Excel to avoid the use of Indirect:

=IF(ISBLANK(INDEX(Cities!A:A,ROW(A2))),INDEX(Repeat!A:A,ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2)),Cities!A2)

Upvotes: 0

player0
player0

Reputation: 1

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)

enter image description here

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)

Upvotes: 2

Related Questions