CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user3347814
user3347814

Reputation: 1133

How to make a range repeat n-times in Google SpreadSheet

I use ArrayFormula() to simplify the way I create my reports.

Instead of having to create a reference in each cell (eg. =C1,=C2,=C3,=C4 in each cell, I just use =arrayformula(C1:C4) in one single cell. It does exactly same job, but is much simpler and it keeps things more organized, because I just need to look in one cell for possible errors.

It works great when I have to reference a range into another like take the values of C1:C4 into the A1:A4 range. In the A1 cell I would just write =arrayformula(C1:C4) and it does its magic.

It does get a bit trickier when the ranges are not the same length, but it is feasible nonetheless. For instance, if I want to stack two or more range link C1:C4 on top of B1:B3, on cell A1 I can write =arrayformula({C1:C4;B1:B3}).

My problem is using arrayFormula() to copy a repeating pattern. For instance, if I want to copy the content of cell C1 4 times I would use =arrayformula({C1;C1;C1;C1}).

This would work and would achieve the desired effect. However, I was wondering if there is a better way to do that. Something like =arrayformula({C1}*12) were this pattern would repeat 12 times. This would also enable me to have a dynamic formula, such as =arrayformula({C1}*count(D:D)) where the pattern would repeat according to some variable.

Do you have any ideia on how to achieve that using only native formula (no javascript)?

Upvotes: 26

Views: 47517

Answers (5)

vstepaniuk
vstepaniuk

Reputation: 868

For N_rows of value:

=ARRAYFORMULA(IF(SEQUENCE(N_rows),value))

value can be anything here: a string, a number or a formula.

This was suggested here.

Here are some use cases:

N_rows can also be calculated with some other formula: =ARRAYFORMULA(IF(SEQUENCE(COUNTIF(A:A,"Yes"),value))

You can also play with the condition of IF there:

=ARRAYFORMULA(IF(ISODD(SEQUENCE(N_rows)),"odd row","even row"))

or

=ARRAYFORMULA(IF(SEQUENCE(N_rows)<3,"less than 3","greater than or equal 3"))

If you need a two-dimensional output:

=ARRAYFORMULA(IF(SEQUENCE(N_rows,M_columns),value))

You can also play with SEQUENCE parameters start and step.

See SEQUENCE.

Original answer:

For N rows and M columns of string text:

=ARRAYFORMULA("text"&T(SEQUENCE(N_rows, M_columns)))

For N rows and M columns of number 123:

=SEQUENCE(N_rows, M_columns, 123 ,0)

Upvotes: 20

TheMaster
TheMaster

Reputation: 50445

Another way to do this is with IF and SEQUENCE:

=ARRAYFORMULA(IF(SEQUENCE(4),C1))

Before sequence came, there was ROW:

=ARRAYFORMULA(IF(ROW(A1:A4),C1))

The range A1:A4 provides sequence of 1 to 4.

Upvotes: 5

Biro
Biro

Reputation: 21

I think the formula you're looking for is sequence with a zero step.

=sequence(12,1,3,0)

To make it dynamic, just make the number of rows your variable.

=sequence(count(d:d),1,3,0)

Upvotes: 2

Gabriel
Gabriel

Reputation: 21

=arrayformula(count(D:D)*split(rept(C1&";",4), ";"))

This approach won't work when C1 contains a Text Value.

I've come up with a variation to dynamically repeat / fill down a text value in-line with content from another column:

=arrayformula(transpose(split(rept("TEXT|",counta(D:D)), "|")))

Upvotes: 2

G4mo
G4mo

Reputation: 607

I would use split() function instead of arrayformula() and rept() function to repeat cell positions. For example, if your n=4 the formula will look like this:

=split(rept(C1&";",4),";")

rept() repeats cell position C1+semicolon four times creating a string and split() function divides created string by semicolons to horizontal cells.

You can rotate resulted horizontal table to vertical table using transpose() function:

=transpose(split(rept(C1&";",4),";"))

And yes, you can use it to create dynamic formulas with help of arrayformula() function:

=arrayformula(count(D:D)*split(rept(C1&";",4), ";"))

Upvotes: 30

Related Questions