# 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

• 10.3 KB Views: 11

#### Excel Wizard

##### Active Member

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

#### Attachments

• 11.4 KB Views: 9

#### MBS

##### New Member

=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

• 10.6 KB Views: 4