How do I get Google Sheets to repeat data in a cell X times?

Question

I'm a Google Sheet newbie so appreciate any help. I would like to get the names I have in a cell to repeat X amount of times down a column and have it repeat for the following names that I have. I'm not really sure where to start.

Here is an example of what I'd like to do: https://docs.google.com/spreadsheets/d/1aCEi3uo1xztZM6FiXvo6_MW84d9wbawcxJ8NMJFP4aE/edit#gid=0

Thanks for your guidance!

Hi @rainbowx44, have you tried using the `REPT` & `TRANSPOSE` functions for this task? — ale13, May 21 '20 at 15:57
@ale13 - I'm not familiar with those functions but I can try it out! — rainbowx44, May 21 '20 at 16:26

ale13 · Answer 1 · 2020-05-22T13:55:34.420

Method 1

You can use this formula:

={INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,1);INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,2);INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,3);INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,4);INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,5);INDEX(TRANSPOSE(ARRAYFORMULA(SPLIT(REPT(A1:A6,B1)," "))),0,6)}

This one makes use of the below formulas:

REPT for repeating the data B1 times;
SPLIT for splitting the data;
TRANSPOSE for transposing the resulting rows;
INDEX for merging the resulting columns into a single one;

Note

A blank space has been added after each name in order to make splitting easier and more convenient.

After the formula is being applied, this is how it will look like:

Method 2

You can make use of Apps Script - Apps Script is a powerful development platform which can be used to build web apps and automate tasks. What makes it special is the fact that it is easy to use and to create applications that integrate with G Suite.

Therefore, your task can be accomplished by using this script:

function multiplyValues() {
   var ss = SpreadsheetApp.openById("ID_OF_THE_SPREADSHEET").getSheetByName("SHEET_NAME");
   var rangeVals = ss.getRange("A1:A6");
   var nameValues = rangeVals.getValues();
   var multValue = ss.getRange("B1").getValue();
   var arrayVals = [];

   for (var i = 0; i < nameValues.length; i++)
      for (var j = 1; j <= multValue; j++) 
         arrayVals.push(nameValues[i][0]);

   for (var i = 0; i < arrayVals.length; i++) 
      ss.getRange(i+1, 4).setValue(arrayVals[i]);
}

Explanation

The above script is bulding the array by multiplying each value with the value from the B1 cell and afterwards is pasting its contents on the 4th column. In order to get the values the getRange and getValues methods have been used and to paste the new array in the column the setValue has been used.

Reference

I also recommend you to take a look at the links below since they might be of help to you.

Consider moving your answer to [here](https://stackoverflow.com/questions/73880042/repeat-whole-row-n-times-based-on-column-value-in-google-sheets) for better exposure. — TheMaster, Oct 20 '22 at 07:04

How do I get Google Sheets to repeat data in a cell X times?

1 Answers1

Method 1

Method 2

Reference