How to repeat row N times and increment date by 1 for each new row in Google Sheets?

Question

I have rows with start and end date. I need to repeat each row N times and increment new date column by one. N = the number of days between the start date and en date

My table:

Column A	Start date	End date
A	10/09/2022	12/09/2022
B	15/09/2022	16/09/2022
C	08/09/2022	12/09/2022

The result I'd like to generate automatically (new row will often be added):

Column A	Start date	End date	Date
A	10/09/2022	12/09/2022	10/09/2022
A	10/09/2022	12/09/2022	11/09/2022
A	10/09/2022	12/09/2022	12/09/2022
B	15/09/2022	16/09/2022	15/09/2022
B	15/09/2022	16/09/2022	16/09/2022
C	08/09/2022	12/09/2022	08/09/2022
C	08/09/2022	12/09/2022	09/09/2022
C	08/09/2022	12/09/2022	10/09/2022
C	08/09/2022	12/09/2022	11/09/2022
C	08/09/2022	12/09/2022	12/09/2022

I hope my need is clear.

Thanks,

I've tried THIS, but the solution is for fixed N times while I need N to be dynamic.

UPDATE

I though it'll be easy to reproduce the solution to my exact need, but it's not the case... I've received two great solutions which work with my first example, but not the full need.

Here is an example of the exact need:

Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Start date	End date	Col11	Col12	Col13	Col14	Col15	Col16	Col17	Col18	Col19
A	B	C	D	E	F	G	H	10/09/2022	24/09/2022	K	L	M	N	O	P	Q	R	S
T	U	V	W	X	Y	Z	A	05/10/2022	17/11/2022	D	E	F	G	H	I	J	K	L

z'' · Answer 1 · 2022-12-15T18:10:36.140

Here's one way to do that using REDUCE.

=ARRAYFORMULA(
  {A1:C1,"Date";
   QUERY(
    REDUCE(
      {"","","",""},
       BYROW(
         FILTER(A2:C,A2:A<>"",B2:B<>"",C2:C<>""),
         LAMBDA(row,JOIN("❄️",row))),
       LAMBDA(acc,cur,
         {acc;
         LAMBDA(a,start,end,
           LAMBDA(dif,
             {IF(dif,{a,start,end}),SEQUENCE(MAX(dif),1,start)})
             (SEQUENCE(end-start+1)))
          (INDEX(SPLIT(cur,"❄️"),,1),
           INDEX(SPLIT(cur,"❄️"),,2),
           INDEX(SPLIT(cur,"❄️"),,3))})),
    "OFFSET 1",0)})

UPDATE

=ARRAYFORMULA(
  QUERY(
    {TRIM(QUERY(
            SPLIT(REDUCE(,
                    BYROW(A2:S3,LAMBDA(row,JOIN("❄",row)))&"♥"&J2:J3-I2:I3+1,
                    LAMBDA(
                        acc,cur,
                        {acc;
                         IF(SEQUENCE(INDEX(SPLIT(cur,"♥",,),,2)),
                            INDEX(SPLIT(cur,"♥",,),,1))})),"❄",,),
            "OFFSET 1",0)),
    QUERY(
      FLATTEN(MAP(I2:I3,J2:J3,LAMBDA(start,end,SEQUENCE(1,end-start+1,start)))),
      "WHERE Col1 IS NOT NULL")},
    "SELECT Col"&JOIN(", Col",SEQUENCE(COLUMN(J1)))&
    ", Col"&COLUMNS(A2:S3)+1&
    ", Col"&JOIN(", Col",SEQUENCE(COLUMNS(A2:S3)-COLUMN(J1),1,COLUMN(J1)+1))))

Thanks @ztiaa, it works for my example! But I'd should have provided the full need instead of a simpler example, because I couldn't manage to adapt your solution to my full need. I've updated my question. — Colas dri, Dec 15 '22 at 15:59
Thanks again! *Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1222. Actual: 1221.* I've created a document to make things easier https://docs.google.com/spreadsheets/d/1AiBmfSoJcze8kd1De_tW0TV9C8UxQs5nloYQOsM-QRA/edit#gid=1324050944 — Colas dri, Dec 15 '22 at 19:45

score 0 · Answer 2 · answered Dec 12 '22 at 18:38

0

try:

=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(A2:A&"|"&B2:B&"|"&C2:C&"|"&MAP(B2:B,C2:C,LAMBDA(bx,cx,if(bx="",,TRANSPOSE(SEQUENCE(DATEDIF(bx,cx,"d")+1,1,bx,1)))))),"|")),"Select * Where Col4 IS NOT NULL")

-

answered Dec 12 '22 at 18:38

rockinfreakshow

15,077
3
12
19

Thanks @rockinfreakshow, it works for my example! But I'd should have provided the full need instead of a simpler example, because I couldn't manage to adapt your solution to my full need. I've updated my question. – Colas dri Dec 15 '22 at 15:57
Hi.. sorry its a bit unclear from your updated info on what's the input and expected output. it would help if you could share a sample sheet with realistic demo data. Cool – rockinfreakshow Dec 15 '22 at 16:43
I've created a document where my data is in "Data" sheet. --> https://docs.google.com/spreadsheets/d/1AiBmfSoJcze8kd1De_tW0TV9C8UxQs5nloYQOsM-QRA/edit#gid=1276086066 – Colas dri Dec 15 '22 at 19:47

How to repeat row N times and increment date by 1 for each new row in Google Sheets?

UPDATE

2 Answers2