Google-sheets – way to generate many rows and columns from a single value

formulasgoogle sheetsgoogle-sheets-arrays

Let's say I have a sheet with a single column that lists all of my clients with each row being a unique value: e.g: client1,client2,client3,client4,…

Goal: I'd like that in a separate sheet, 2 columns and 12 rows will be generated for each client and that they'd be filled in the following way:

client 1, 1
client 1, 2
client 1, 3
...
...
client 1, 12
client 2, 1
...
client 2, 12
...

I think that if someone will be able to help me get this one done, I'd be able to achieve my final goal which is for each client, generate 365 unique rows for each day of the year like so:

(client, month, day, year)
client 1, 1, 1, 2020
client 1, 1, 2, 2020
client 1, 1, 3, 2020
...
...
client 1, 12, 31, 2020

Important: Even though we can use a google sheet script to generate all of the rows, I'd really like to avoid that.

Best Answer

In Example there is formula

={TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(transpose(E1:E8)&",", $A$2))), ",")),
TRANSPOSE(SPLIT(REPT(JOIN(",",TRANSPOSE(SEQUENCE(A2,1,B2,1)))&",",COUNTA(E1:E8)),","))}

First part of formula generates repeating list of clients, where E1:E8 - initial range of clients, $A$2 - cell with number of repeats you need

=TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(transpose(E1:E8)&",", $A$2))), ","))

Second part of formula generates dates according to start date (B2), number of days (A2) and number of clients(COUNTA(E1:E8))

=TRANSPOSE(SPLIT(REPT(JOIN(",",TRANSPOSE(SEQUENCE(A2,1,B2,1)))&",",COUNTA(E1:E8)),","))
Related Topic