1

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

TheMaster
  • 45,448
  • 6
  • 62
  • 85
geekyfreaky
  • 39
  • 1
  • 7
  • The original formula worked fine for me - the only issue I had with it was that it had the wrong sort of double quotation marks and I had to edit those =IF(ISBLANK(INDIRECT(“Cities!A”&ROW(A2))),INDIRECT(“Repeat!A”&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2) – Tom Sharpe Jan 19 '19 at 12:51
  • 1
    @player0 [tag:spreadsheet] is for questions about spreadsheet apps that don't have an specific tag. – Rubén Jan 20 '19 at 02:03

5 Answers5

2

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,IF(c,{a;rg_to_repeat}))
  )
)(A1:A4,4)

Advantage:

  • Works even with 2D arrays.
  • No string manipulation.
  • Auto filling array formula.
TheMaster
  • 45,448
  • 6
  • 62
  • 85
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)
player0
  • 124,011
  • 12
  • 67
  • 124
0

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)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

You might copy down from Row1:

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

for as many sets of four as suits you.

pnuts
  • 58,317
  • 11
  • 87
  • 139
0

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)))
M. Hoffman
  • 56
  • 4