Repeat Cell values in columns based on Start and End dates

Question

I have data in the below format (Cols B:E):

What I am trying to do is create a new table (Cols L:N) where Date repeats in a single column and the corresponding data , ie, Emp Name and Leave type repeats. I was able to do the same just for 'Date' using the below formula: "=ARRAYFORMULA(QUERY(FLATTEN(IF(DAYS(D4:D32,C4:C32)>=SEQUENCE(1,1000,0),C4:C32+SEQUENCE(1,1000,0),"")),"where Col1 is not null"))"

How can this be achieved using formula? Attaching the required format as well:

didn't you request GAS solution?

player0
– player0

2023-02-01 19:51:25 +00:00
Commented Feb 1, 2023 at 19:51 — player0
– player0, Commented Feb 1, 2023 at 19:51

rockinfreakshow · Accepted Answer · 2023-02-01 14:16:19Z

1

please try:

=QUERY(INDEX(SPLIT(FLATTEN(B2:B&"|"&E2:E&"|"&MAP(C2:C,D2:D,LAMBDA(c,d,SEQUENCE(1,DAYS(d,c)+1,c,1)))),"|",0,0)),"Select Col1,Col3,Col2 WHERE Col3 IS NOT NULL AND Col2<>''")

answered Feb 1, 2023 at 14:16

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Repeat Cell values in columns based on Start and End dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related