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.

2 Answers 2


In Example there is formula

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

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

  • Nice! I definitely learned new things with your method. Thanks!
    – Dror
    Mar 10, 2020 at 7:51

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


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:


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:


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.


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

  • "If anyone happens..." that doesn't should be included in the answer, IMHO there are two options. 1. Edit your question to include your findings as a progress update of your search/research efforts 2. Post a follow up question. Jan 25, 2020 at 18:27
  • All good, I've just shared the sheet though I'm still happy to get a better solution.
    – Dror
    Jan 25, 2020 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.