Nested textjoin function without macro

[avid.excel.user]

Dear Friends

Please help me with a solution without macro if possible.

My data looks like this :

Acc_No	Text
123​	Today is Saturday Tomm is Sunday
123​	Tomm it will rain
234​	- Today it is sunny
345​	Lets look at the watch and decide. Maybe it will be a good time to catch up.
345​	Hope this is great
345​	Wish you all the best
456​	This might work
456​
456​	May not work
456​	Not sure if it does
456​	Lets hope for it to work
456​
678​	All the best
789​	I pray it work
789​	Someone please help me

The result should be as follows:

Acc_No	Text (By text join manually) - very time consuming	Result - should be automated in this format
123​	Today is Saturday Tomm is Sunday | Tomm it will rain	Today is Saturday | Tomm is Sunday | Tomm it will rain
234​	- Today it is sunny	Today it is sunny
345​	Lets look at the watch and decide. Maybe it will be a good time to catch up. | Hope this is great	Lets look at the watch and decide | Maybe it will be a good time to catch up | Hope this is great
456​	This might work | May not work | Not sure if it does | Lets hope for it to work	This might work | May not work | Not sure if it does | Lets hope for it to work
678​	All the best	All the best
789​	I pray it work | Someone please help me	I pray it work | Someone please help me

I understand that the text clean up is a challenge, since, i am unable to do a conditional nested Text join I am struggling to copy paste the formulae and choose the rows individually.

Request if any solution be it with out or with macro should work.

Need urgent assistance. Will be grateful.

 

[jasonb75]

Please update your account details to show which version of excel you are using. Some newer versions contain functions that will allow you to do this without vba, older versions do not.

 

[avid.excel.user]

Hi Jason

Thank you for the reply

My current version is Microsoft Excel 365

 

[jasonb75]

With office 365 this should work

Book1
	A	B	C	D
1	Acc_No	Text
2	123	Today is Saturday Tomm is Sunday	123	Today is Saturday | Tomm is Sunday | Tomm it will rain
3	123	Tomm it will rain	234	- Today it is sunny
4	234	- Today it is sunny	345	Lets look at the watch and decide. Maybe it will be a good time to catch up. | Hope this is great | Wish you all the best
5	345	Lets look at the watch and decide. Maybe it will be a good time to catch up.	456	This might work | May not work | Not sure if it does | Lets hope for it to work
6	345	Hope this is great	678	All the best
7	345	Wish you all the best	789	I pray it work | Someone please help me
8	456	This might work
9	456
10	456	May not work
11	456	Not sure if it does
12	456	Lets hope for it to work
13	456
14	678	All the best
15	789	I pray it work
16	789	Someone please help me
Sheet2

Cell Formulas
Range	Formula
C2:C7	C2	=UNIQUE(A2:A16)
D2:D16	D2	=SUBSTITUTE(TEXTJOIN(" | ",1,IF($A$2:$A$16=C2,$B$2:$B$16&"","")),CHAR(10)," | ")
Dynamic array formulas.

 

[avid.excel.user]

Wow this works amazing

Thank you very much