Hi Jawad,
Try this,
1. Remove merge & Center.
it will come like this
1 ABC
2
3
4 DES
5
6
2. after that select full data press F5 SELECT special select blanks and click ok, then selected cell put "="select the above cell and prese ctrl+ENTER..
View attachment 44522
1] In H4, formula copy down :
=IFERROR(TRIM(MID(SUBSTITUTE(CHAR(10)&INDEX(B$4:B$6,MATCH(I4,C$4:C$6,0)),CHAR(10),REPT(" ",50)),COUNTIF(I$4:I4,I4)*50,50)),"")
2] In I4, formula copy down :
=IFERROR(INDEX(C$4:C$6,AGGREGATE(15,6,(ROW(C$4:C$6)-ROW(C$3))/(LEN($B$4:$B$6)-LEN(SUBSTITUTE($B$4:$B$6,CHAR(10),))+1>=COLUMN($A:$J)),ROW(A1))),"")
Regards
Bosco
Bosco, Thank You so much. Formula helped. Can you also tell me why "ROW(A1)" in the second formula
Please see attached revised file with revised formulaBosco,
I tried same formula on this data. It is not working on column H.
Because, instead of "ALT+Enter", numbers are separated by Space
Please see attached revised file with revised formula
Regards
Bosco