Multiple row contents into one row

[assainar kutty]

Hi,
I have a list of Catalog which contains more than 5000 numbered items as shown below sample. Colum A is Material number and Colum B is the detailed description of material . The description in Colum B is written in one or more rows. I,e 90100100(A2) having 3 rows(B2:B4), the next material number (A5) having 4 rows(B5:B8)….. I need to apply a formula that Contents of all the rows of item A2 in one row in column C2 and contents of the rows of item A5 in one row in column C5…….till end of the list. Each row to be separated with a comma(,).

Is there any formula or macro to fill for all the materials?

Material Detailed Description

90100100 RANGE 3 CONDUCTOR BOX X PIN
309.72 LB/FT (1.000" WT)
XLF CONNECTIONS
90100101 COND,30,310#,X52,XLF B,W/FS,R3
RANGE 3 CONDUCTOR BOX X FLOAT SHOE
309.72 LB/FT (1.000" WT)
XLF BOX X WELD ON FLOAT SHOE
90100102 RANGE 3 CONDUCTOR BOX X PIN WITH STABILIZER FINS TYPE ?
309.72 LB/FT (1.000" WT)
XLF CONNECTIONS
W/STABILIZER FINS WELDED ON
STARTER HEAD

90100103 RANGE 3 CONDUCTOR BOX X PIN WITH CAMERON LOAD RING
309.72 LB/FT (1.000" WT)
XLF CONNECTIONS
90100104 RANGE 3 CASING BOX X PIN
87.5 LB/FT (0.435" WT)
BIG OMEGA CONNECTIONS (WITH K-55 COUPLING)


With many thanks

Kutty

[NBVC]

It would be to complex to do this with native Excel functions...

You could probably create VBA code to do it.....

If you are willing to add a free addin to Excel called Morefunc.xll from this site: http://xcell05.free.fr/morefunc/english/ ,which is used by many of us in this and other forums, then you can apply the following formula to E3:

=MCONCAT(IF(MATCH(TRUE,($A3:$A$40<>""),0)+1<=MATCH(TRUE,($A4:$A$41<>""),0)+1,B3:INDEX($B3:$B$40,MATCH(TRUE,($A4:$A$41<>""),0)),""),", ")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula. Then you can copy it down.

If you adjust any ranges, etc.. you need to re-confirm the formula.

Note: In order to avoid error on the last row.. enter anything (e.g. "X" in column A of the row after your last entry in column B.

See attached... (won't work if you don't have the addin installed).

[assainar kutty]

Hi,

Thanx for the quick response. I tried to download the excel addin from http://xcell05.free.fr/morefunc/english/ but unable to download. below error msg is generating while downloading.

Warning: main(./common/init.php) [function.main]: failed to open stream: No such file or directory in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

Fatal error: main() [function.require]: Failed opening required './common/init.php' (include_path='/mnt/108/sdc/2/4/xcell05/include:.:/usr/php4/lib/php') in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

How to solve this please advise.
With Regards,

Kutty

[NBVC]

Perhaps try downloading from here:

http://www.download.com/Morefunc/300...-10423159.html

If still not working.. post the problem at their forum: http://xcell05.free.fr/forums/viewforum.php?id=1

[assainar kutty]

Hi,

it is amazing. excellent solution its working amazinlgly.
it would be very helpfull for those working with spreadsheets imported from SAP reports, crystal reports etc....

thanx a lot for your unstinting instant supports.

Kutty

[NBVC]

Thank you very much for the positive feedback.