Repeat cell values and increment them afterwards

Question

I'm trying to set up a structure in a sheet that allows for a date value to be incrementally (monthly) increased from a given start date. However, I also want each month to be repeated x times before it moves on to the next month, which should then also be repeated x times before moving on, and so on, until a given end date.

I've created this sheet to exemplify my desired output.

In the Example tab, I have a list of names (Sales reps) which, in this case, correspond to the "x" I mentioned previously. This means that I would like each date value to be repeated the same number of times as there are sales reps, before moving on to the next month and repeating it again the same number of times as there are sales reps. This also means that, if I increase the number of sales reps at some point, that should also be reflected in the number of times that a month is repeated. Also, the start and end dates should match the values in D3 and D5.

In columns D and E in the Example tab, I just wrote the kind of behaviour I'm looking for in static values. Can anyone help me fill in the Desired solution tab with a formula that automatically achieves that? That would be much appreciated 🙏🏻😃

---

EDIT: THINGS TRIED SO FAR

I created a new tab called Undesired solution where I show a hacky workaround that I tried, but that I would like to keep away from. The main reason for that is that, if the number of sales rep was to change:

• I would have to manually update the first few cells of column F
• I would have to manually update the formula used from G5 onwards

Answer 1

Here's how it looks filled with an array formula. I'm using counta() to get the number of names and month() to get the difference between start and end date in months, then applying sequence with int and mod to get the index of the required date and name, finally applying edate and vlookup to get the actual date and name.

=ArrayFormula({edate(D3,int(sequence(COUNTA(B3:B)*(month(D5)-month(D3)+1),1,0)/COUNTA(B3:B))),
vlookup(mod(sequence(COUNTA(B3:B)*(month(D5)-month(D3)+1),1,0),COUNTA(B3:B)),{sequence(rows(B3:B),1,0),B3:B},2)})