I have a wks for the description of stock items but the description is in 40
character groups so for one item number I have multiple rows, I need a way to
merge these rows into one cell per stock number.
example
Stock # description
s1 shoe, men
s1 style rbs
s1 color :brown

I need

s1 Shoe,men style rbs color brown

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=TRIM(MCONCAT(IF(A2:A4="s1",B2:B4,"")&" "))

ctrl+shift+enter, not just enter


"Real" wrote:

I have a wks for the description of stock items but the description is in 40
character groups so for one item number I have multiple rows, I need a way to
merge these rows into one cell per stock number.
example
Stock # description
s1 shoe, men
s1 style rbs
s1 color :brown

I need

s1 Shoe,men style rbs color brown

Thank you very much for your help this did work but as you might expect I
still need your help as my situation is a little more complex, this is the
file I have
s1 decrip_s1_l1 decrip_s1_l1decrip_s1_l2decrip_s1_l3
s1 decrip_s1_l2
s1 decrip_s1_l3
s2 decrip_s2_l1 decrip_s2_l1decrip_s2_l2
s2 decrip_s2_l2
s3 decrip_s3_l1 decrip_s3_l1decrip_s3_l2decrip_s3_l3decrip_s3_l4de crip_s3_l5
s3 decrip_s3_l2
s3 decrip_s3_l3
s3 decrip_s3_l4
s3 decrip_s3_l5
s4 decrip_S4_l1 decrip_S4_l1

I have many thousands of items and the lenght of the description varies for
each item, some have one line of description while others have 25 lines.....

So I need to have the formula find all the description linked to the same
stock number and then MCONCAT these rows into one cell.

Ideally I would mcomcat only once per Stock number but it is also OK if we
concat on all lines.

Thanks

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=TRIM(MCONCAT(IF(A2:A4="s1",B2:B4,"")&" "))

ctrl+shift+enter, not just enter


"Real" wrote:

I have a wks for the description of stock items but the description is in 40
character groups so for one item number I have multiple rows, I need a way to
merge these rows into one cell per stock number.
example
Stock # description
s1 shoe, men
s1 style rbs
s1 color :brown

I need

s1 Shoe,men style rbs color brown