1

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.

0

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!

  • "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. – Rubén Jan 25 at 18:27
  • All good, I've just shared the sheet though I'm still happy to get a better solution. – Dror Jan 25 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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