• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

MBS

New Member
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
 

Attachments

MBS

New Member
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

Well-Known Member
This is a 365 insider beta solution, so not generally available yet.
Code:
= 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)
 

Attachments

Top