CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
ZxNuClear
ZxNuClear

Reputation: 236

Repeat multiple columns

How to implement multiple column repetition using formulas? Here is my sheet. For example, I need to repeat the range A1:C5 N-times so that the result is like in the range E1:G25. Thanks for the help.

Upvotes: 0

Views: 337

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Another way, number of reps in D1, number of rows and columns in range to be copied can vary:

=ArrayFormula(vlookup(
mod(quotient(sequence(D1*rows(A1:C5),columns(A1:C5),0),columns(A1:C5)),rows(A1:C5))+1,
{row(A1:C5),A1:C5},
mod(sequence(D1*rows(A1:C5),columns(A1:C5),0),columns(A1:C5))+2))

enter image description here

For your locale:

=ArrayFormula(vlookup(
mod(quotient(sequence(P1*rows(A1:C5);columns(A1:C5);0);columns(A1:C5));rows(A1:C5))+1;
{row(A1:C5)\A1:C5};
mod(sequence(P1*rows(A1:C5);columns(A1:C5);0);columns(A1:C5))+2))

Upvotes: 0

player0
player0

Reputation: 1

try:

={OFFSET(A1:C;;;COUNTA(A1:A));
  OFFSET(A1:C;;;COUNTA(A1:A));
  OFFSET(A1:C;;;COUNTA(A1:A));
  OFFSET(A1:C;;;COUNTA(A1:A));
  OFFSET(A1:C;;;COUNTA(A1:A))}

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36840

I will try to simplify below formula but it works-

=ArrayFormula(SPLIT(FLATTEN(SPLIT(REPT(ArrayFormula(TEXTJOIN("#";FALSE;"#"&A1:A5&"@"&B1:B5&"@"&C1:C5));5);"#"));"@";;FALSE))

See your sheet

enter image description here

Upvotes: 1

Related Questions