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:

CodePudding user response：

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<>''")