NickName:geekyfreaky Ask DateTime:2019-01-19T19:04:58

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

Copyright Notice：Content Author:「geekyfreaky」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/54266412/repeat-range-of-items-multiple-times-in-google-sheets

Answers

player0 2019-01-19T11:41:29

this will work only if you paste it into A5 of the same sheet and drag it down:\n\n=IF(ISBLANK(INDIRECT(ROW(A1))),INDIRECT((ROWS($A$1:A4)-(COUNTA(A:A)-2))),A1)\n\n\n\n\notherwise, you can use:\n\n=QUERY({Sheet1!A1:A4;Sheet1!A1:A4;Sheet1!A1:A4},\"select *\",0)\n\n\nor:\n\n=TRANSPOSE(SPLIT(REPT(JOIN(\",\",Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4&\",\"),3),\",\",1))\n\n\nor: \n\n=TRANSPOSE(SPLIT(REPT(Sheet1!A1&\",\"&Sheet1!A2&\",\"&Sheet1!A3&\",\"&Sheet1!A4&\",\",3),\",\",1))\n\n\nor:\n\nfunction REPEAT(range,amount,header) {\n var output = [];\n\n // check if range is cell\n if(typeof range == 'string') {\n for(var i=0; i<amount; i++) {\n output.push([range]);\n }\n return output;\n } else {\n // check if header is wanted\n var value;\n if(header == 1) {\n output.push(range[0]);\n value=header;\n } else if(header == \"\") {\n value=0;\n } else {\n value=0;\n } \n for(var i=0; i<amount; i++) {\n for(var j=value, jLen=range.length; j<jLen; j++) {\n output.push(range[j]);\n }\n } \n return output;\n } \n}\n\n\n\n\n=REPEAT(Sheet1!A1:A4,3,0)\n",

TheMaster 2022-09-30T09:25:56

You can create vertical arrays using array literals {;}. You can automate this process by creating a loop using REDUCE.\n=LAMBDA(rg_to_repeat,times,\n REDUCE(\n rg_to_repeat,\n SEQUENCE(times-1),\n LAMBDA(a,c,IF(c,{a;rg_to_repeat}))\n )\n)(A1:A4,4)\n\nAdvantage:\n\nWorks even with 2D arrays.\nNo string manipulation.\nAuto filling array formula.\n",

Tom Sharpe 2019-01-19T13:03:23

I might as well add this as an answer:\n\n=IF(ISBLANK(INDIRECT(\"Cities!A\"&ROW(A2))),INDIRECT(\"Repeat!A\"&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))),Cities!A2)\n\n\nworks fine in Google Sheets and Excel.\n\nThis also works and may be preferable in Excel to avoid the use of Indirect:\n\n=IF(ISBLANK(INDEX(Cities!A:A,ROW(A2))),INDEX(Repeat!A:A,ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2)),Cities!A2)\n",

pnuts 2019-05-29T20:39:19

You might copy down from Row1:\n\n=offset(Sheet1!A$1,mod(row()-1,4),)\n\n\nfor as many sets of four as suits you.",