Google Sheets - Sequence Dates across a table of data?

Question

I have been able to use Google Sheet's SEQUENCE formula to provide me with sequenced dates for Partner 1 but then the formula stops and doesn't move on to Partner 2.
I have tried nesting within an Google's ArrayFormula but still cannot get it to function properly.

I'd like to avoid having to do each partner manually then copying and pasting each partner below the other.

Formulas I've tried:

For creating partner repeats based on number of days

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT($L$3:$L$16&",",$Q$3:$Q$16),,2^100),","))))

For sequencing dates, but only does the first range

 =ARRAYFORMULA(TO_DATE(SEQUENCE(Q3,1,O3)))

CURRENT DATA

| Partner| Start Date| End Date | Total Days|
|:-------|:---------:|:--------:|----------:|
|Partner1|  3/1/2021 |  3/8/2021|   7       |
|Partner2|  3/1/2021 |  3/9/2021|   8       |

WANTED FORMAT

Partner     Date        
Partner 1   3/1/2021        
Partner 1   3/2/2021        
Partner 1   3/3/2021        
Partner 1   3/4/2021        
Partner 1   3/5/2021        
Partner 1   3/6/2021        
Partner 1   3/7/2021        
Partner 1   3/8/2021        
Partner 2   3/1/2021        
Partner 2   3/2/2021        
Partner 2   3/3/2021        
Partner 2   3/4/2021        
Partner 2   3/5/2021        
Partner 2   3/6/2021        
Partner 2   3/7/2021        
Partner 2   3/8/2021        
Partner 2   3/9/2021

user_1527780 · Answer

You can use the following formula

=INDEX({SPLIT(A2&"@"&SEQUENCE(C2-B2,1,B2),"@");
        SPLIT(A3&"@"&SEQUENCE(C3-B3,1,B3),"@");
        SPLIT(A4&"@"&SEQUENCE(C4-B4,1,B4),"@")})

If still having issues, please share more info.

(do adjust ranges and locale syntax to meet your needs)

user_3894917 · Answer

You can do it generally by doing a vlookup of sequence number in the cumulative sum of the days.

For partner:

=ArrayFormula(vlookup(sequence(sum(D2:D),1,0),{sumif(row(D2:D),"<"&row(D2:D),D2:D),A2:A},2,true))

For date:

=ArrayFormula(to_date(sequence(sum(D2:D),1,0)
-vlookup(sequence(sum(D2:D),1,0),sumif(row(D2:D),"<"&row(D2:D),D2:D),1,true)
+vlookup(sequence(sum(D2:D),1,0),{sumif(row(D2:D),"<"&row(D2:D),D2:D),B2:B},2,true)))

Google Sheets - Sequence Dates across a table of data?

2 Answers