CODEWITHSUNDEEP
google-sheets
Nimink0
Nimink0

Reputation: 330

How to repeat row range N times in Google Sheets

Basically, I have N rows with one unique value always repeating three times. This is col_1. Then I have a range of values I want repeated as many times there are unique values in col_1. This needs to be dynamic, since col_1 is automatically generated from a list.

col_1 |        values
-------        ------
  a   |           d
  a   |           e
  a   |           f
  b   |
  b   |
  b   |
  c   |
  c   |
  c   |

So this is what I want to end up with:

col_1 |  col_2
----------------
  a   |    d
  a   |    e
  a   |    f
  b   |    d
  b   |    e
  b   |    f
  c   |    d
  c   |    e
  c   |    f

Edit: as a note in comment, my data is completely dynamic so I can't have any assumptions about how many rows there will be. In here I have a list of [a,b,c], multiplied by as many times there are items in Values, so [a,b,c] & [d,e,f] results in 9 rows. If I add "g" to [d,e,f], I then have 12 rows and if I then add "h" to [a,b,c] I would have 16 rows. The dynamic part is the important bit in here.

Upvotes: 2

Views: 3383

Answers (2)

MattKing
MattKing

Reputation: 7773

There is a new function that we discovered on the Google Product forums due to a user's post. That function is called FLATTEN().

in your scenario, this should work:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A&"|"&TRANSPOSE(C2:C4)),"|",0,0),"where Col1<>''"))

Upvotes: 1

Nimink0
Nimink0

Reputation: 330

So I want to answer my own question, because I spend way too long for looking the answer and couldn't find one, so I just came up with one by myself. So here's the answer:

=ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C4&"~"),COUNTA(UNIQUE(A2:A500))),"~")))

You can just copy and change the ranges for it to work, but let me explain how does it work.

First we combine the values we want to repeat into one string with CONCATENATE. The three values are defined in the range of C2:C4.

CONCATENATE(C2:C4&"~") → "d~e~f~"

~ is used here as a delimiter, so there's no any special tricks in here. Next we repeat this string we just made as many times as there are unique values in col_1. For this we use a combination of COUNTA, UNIQUE and REPT.

COUNTA(UNIQUE(A2:A500)) ← Count how many unique occurrences there are in a range ( 3 )

REPT(CONCATENATE(C2:C4&"~"),COUNTA(UNIQUE(A2:A500))

Basically this is converted into:
REPT("d~e~f~",3) → "d~e~f~d~e~f~d~e~f~"

Now we have as many d, e and f as we want. Next we need to turn them into cells. We'll do this with a combination of SPLIT and TRANSPOSE.

TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C4&"~"),COUNTA(UNIQUE(A2:A500))),"~"))

We split the string from "~" so we'll end up with an array looking like [d,e,f,d,e,f,d,e,f]. We then need to transpose it to turn it into rows instead of columns.

Last part is to wrap everything into an arrayformula, so the formula actually does work.

=ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE(C2:C4&"~"),COUNTA(UNIQUE(A2:A500))),"~")))

Now the array will look like:

col_1 |  col_2
----------------
  a   |    d
  a   |    e
  a   |    f
  b   |    d
  b   |    e
  b   |    f
  c   |    d
  c   |    e
  c   |    f

Now any time you add a new unique value to col_1, three new values are added

Upvotes: 3

Related Questions