Join or merge text in multiple rows to single row by formula

[MBS]

in attached sheet there is data in in merged and unmerged rows. How to join the texts in one cell from multiple cells, based on data at merged cells/ WITH THE HELP of formulae

 

[Excel Wizard]

Please try

=LET(z,D4:E10,d,INDEX(z,,1),t,INDEX(z,,2),CHOOSE({1,2,3},SEQUENCE(COUNT(d)),FILTER(d,d),FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>",d>0)&" "&t),"/m","x",1)&"</m></x>","//m")))

 

[MBS]

Please try

=LET(z,D4:E10,d,INDEX(z,,1),t,INDEX(z,,2),CHOOSE({1,2,3},SEQUENCE(COUNT(d)),FILTER(d,d),FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>",d>0)&" "&t),"/m","x",1)&"</m></x>","//m")))

Many Thanks @Excel Wizard
It works. It will be great if there is way to elaborate and understand this formula in step by step manner, with sheet
Thanks again

 

[Peter Bartholomew]

This is a 365 insider beta solution, so not generally available yet.

= LET(
      filledDate, FillDownλ(date),
      MAP(distinctDate#,
         LAMBDA(distinct,
            TEXTJOIN(",",,FILTER(text, filledDate=distinct))
   ) ) )
  
where FillDownλ is given by
= LAMBDA(x,SCAN("",x,LAMBDA(y,z,IF(ISNUMBER(z),z,y))))

and the distinct dates are
= FILTER(date,date)