How do I make pic1 look like pic2

[Mohammed Jawad]

How do I make pic1 look like pic2?

 

[AlanSidman]

upload a sample file with a before and after scenario. No one really wants to retype your data to help you and guess at the formatting. Help us to help you.

 

[Syedali]

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..

 

[Mohammed Jawad]

Syedali,
Cells are not merged

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..

 

[Mohammed Jawad]

I have attached a file

 

[bosco_yip]

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

 

[Mohammed Jawad]

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

 

[Mohammed Jawad]

Bosco, Thank You so much. Formula helped. Can you also tell me why "ROW(A1)" in the second formula

Bosco,
I tried same formula on this data. It is not working on column H.

Because, instead of "ALT+Enter", numbers are separated by Space

 

[bosco_yip]

Bosco,
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

 

[Mohammed Jawad]

Please see attached revised file with revised formula

Regards
Bosco

Thank You So Much!!!