How can I repeat an element between 2 dates and indicate the dates
Input values:
| ID | Begin | End |
|---|---|---|
| ST00 | May 15 2022 | May 15 2022 |
| TE01 | May 23 2022 | May 25 2022 |
| TO01 | May 16 2022 | May 19 2022 |
Expected output:
| ID | Date |
|---|---|
| ST00 | May 15 2022 |
| TE01 | May 23 2022 |
| TE01 | May 24 2022 |
| TE01 | May 25 2022 |
| TO01 | May 16 2022 |
| TO01 | May 17 2022 |
| TO01 | May 18 2022 |
| TO01 | May 19 2022 |
What I am doing right now :
in E2
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT(A2:A&"~", if(A2:A="",,C2:C-B2:B 1)),,9^9), "~"))))
in F2 (and dragging below)
=countif(E$2:E2,E2)
in G2
=arrayformula(iferror(vlookup(E2:E,A:B,2,0) F2:F-1))
Is there a way to do whitout column F?
CodePudding user response：
use:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(DAYS(C2:C, B2:B)>=SEQUENCE(1, 1000, ),
IF(A2:A="",,A2:A&"×"&TEXT(B2:B SEQUENCE(1, 1000, ), "mmm dd e")), )), "×"),
"where Col2 is not null", ))
faster:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(DAYS(C2:C, B2:B)>=SEQUENCE(1, MAX(C2:C-B2:B 1), ),
IF(A2:A="",,A2:A&"×"&TEXT(B2:B SEQUENCE(1, MAX(C2:C-B2:B 1), ), "mmm dd e")), )), "×"),
"where Col2 is not null", ))