You can repeat multiple rows n number of times using my REPT_ROWS function, a custom-named function, in Google Sheets.

The n can be the same number or different numbers for each row.

For example, you can repeat the first row 5 times, the second row 2 times, the third row 15 times, and so on, or all the rows ten times.

Earlier, we were using the insert duplicate rows formula for the same. Here I’ve converted that to a simple function for ease of use.

You can straightaway import my REPT_ROWS function from my example Sheet below.

But to use it properly, please follow the examples down below even though you will get it in short form while start typing the function name in a cell.

Example Sheet 81122

Please check How to Create Named Functions in Google Sheets to import it into your Sheet from the above example Sheet.

Syntax and Arguments of the REPT_ROWS Function

The REPT_ROWS() is a custom-named function that contains three arguments. I’ve tried my best to make it simple. I hope you will agree with that.

Syntax: REPT_ROWS(range, rept_by_range, index)

range: The range of rows to repeat (e.g., A2:Z10).

rept_by_range: The column range that contains the number of times each row repeats (e.g., Z2:Z10).

index: The relative position(s) of the output column(s) in the range which is similar to the same argument in VLOOKUP (e.g., 5 to return the fifth column, {1,2,5} to return columns 1, 2, and 5, and sequence(10) to return columns 1 to 10).

Below you can find three examples of using my REPT_ROWS custom-named function to repeat multiple rows n times in Google Sheets.

Repeating Multiple Rows Using the REPT_ROWS Function in Google Sheets

We may use different sets of data for each example. Let’s start with the basic one.

1. Repeat Multiple Rows (Basic Form)

In the following example, the formula in F2 repeats the values in columns B (first name) and C (last name) the number of times specified in D.

=REPT_ROWS(B2:C4,D2:D4,{1,2})

In this, B2:C4 is the range, D2:D4 is the rept_by_range, and {1,2} is the index.

How do we repeat the rows added in the future using the REPT_ROWS function in Google Sheets?

For that, open the ranges in the formula as follows.

=REPT_ROWS(B2:C,D2:D,{1,2}).

The formula will include all the present and future values/rows in columns B to D.

2. Arithmetic Operation

Sometimes we may require a quantity (Qty) column to use in the rept_by_range instead of one dedicated column (D) in example # 1. Here is an example of this.

This time, I want to repeat the rows using the REPT_ROWS function differently in Google Sheets.

I want to duplicate (repeat) the fruit names (Item) based on their quantity (Qty) and adjust the total amount (Amount) to per quantity amount.

For example, apple has two quantities. So duplicate it twice and divide its amount, i.e., 11 by 2.

Formula:

=ArrayFormula(REPT_ROWS({B4:B6,D4:D6/C4:C6},C4:C6,{1,2}))

range: Here the quantity column acts as the rept_by_range, so we do not require that column in the range.

What we want is the Item and Amount/Qty, i.e., {B4:B6,D4:D6/C4:C6}

Related:- How to Use Curly Brackets to Create Arrays in Google Sheets.

rept_by_range: C4:C6

index: The index is {1,2} since we have two columns in the range.

The division operator justifies the ArrayFormula use.

3. Duplicate Rows and Increment Dates by Day, Month, or Year

Here is an example of using the REPT_ROWS named function in Google Sheets with a running count of occurrences.

The purpose is to increment dates in a column after repeating rows.

Assume I signed up for two streaming services to watch movies and TV shows on 1-Jan-2022 and 25-Nov-2022, respectively.

The above two are their monthly subscription dates.

Columns A, B, and C contain the said data. How to get the whole year’s subscription dates by repeating the two rows 12 times each?

We can do it in just three steps.

1. Duplicate B2:C3

First, duplicate the rows in the range B2:C3 12 times each using the following REPT_ROWS named function in cell G2.

=REPT_ROWS(B2:C3,D2:D3,{1,2})

2. Duplicate A2:A3

Then we will repeat the dates in A2:A3 12 times each with the help of the below piece of code in F2.

=REPT_ROWS(A2:A3,D2:D3,1)

3. Incrementing Months in the Date Column

We have already explained how to code a running count array formula in Google Sheets.

Pick a column that contains the unique items in the table.

We will pick the “Description” since the “Amount” and “Date of Renewal” might be the same in some cases (not in this data, though).

Code a running count formula based on that column, and here it is (for the time being, enter it in cell I2).

=ArrayFormula(countifs(G2:G25,G2:G25,row(G2:G25),"<="&row(G2:G25))-1)

To increment the subscription dates by month, follow the below generic formula in F2.

=ArrayFormula(edate(step_2_formula,step_3_formula))

To increment the dates by day, follow the below generic formula in F2.

=ArrayFormula(step_2_formula+step_3_formula)

Follow the first generic formula to increment the dates by year, but the step_3_formula (running count) will be slightly different. Here it is!

=ArrayFormula((countifs(G2:G25,G2:G25,row(G2:G25),"<="&row(G2:G25))-1)*12)

Related:- Incrementing Duplicate Dates by Month or Day in Google Sheets.

That’s all. Thanks for the stay, enjoy!