Is there a way to generate many rows and columns from a single value?

Question

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.

Answer 1

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)),","))

Answer 2

OK it's pretty nasty but I was able to get it done.

Assume the first row we start from is row 2.

The date column is calculated like this with 43831 being 1/1/2020

=-366*FLOOR((ROW()-2)/366,1)+(ROW()-2+43831)

You copy this field infinitely downwards. What happens here is that each January 1st the date is reset so the next client will stay with the same year.

Later we create an "Index" column like so:

=FLOOR((ROW()-2)/366,1)

What happens here is that every 366 days (2020 is a leap year) we move to the next number, e.g: 1->2->3

Lastly, the clients column is calculated like this:

=INDEX(UNIQUE(Sheet1!$A$2:$A, INDIRECT(ADDRESS(ROW(),COLUMN()+1))+2)

with the referenced cell in "INDIRECT" being the index field.

Here's a link handy for anyone who's interested in a working example. Do note that special treatment is needed for supporting more years, including the handling of non-leap years.

https://docs.google.com/spreadsheets/d/1M_rS30gVUdM4YOvisSSk4KFYl_eEdq2AtERcL0ATae0/edit?usp=sharing

If anyone got a better idea, I'm open for suggestions!