How to combine several successive rows into one cell

[gordom]

Hi everyone,
I have two columns filled with data. This is the example:

Column A | Column B
__________|_______________________

name A | description A - line 1
| description A - line 2
| description A - line 3
| description A - line 4

name B | description B - line 1
| description B - line 2

name C | description C - line 1
| description C - line 2
| description C - line 3

name D | description D - line 1
| description D - line 2
| description D - line 3
| description D - line 4
| description D - line 5


Column A consists of list of product names. Not every row in this column
consist of data; there are several blank cells.
Column B consist of products descriptions. Each definition is divided
into several lines (rows).

I would like to combine these separate lines of description into one
cell. As the result I would like to get something like that:

Column A | Column B
__________|______________________________________________

name A | description A - line 1, line 2, line 3, line 4
name B | description B - line 1, line 2
name C | description C - line 1, line 2, line 3

How can it be achieved?
Thanks in advance for all your help. Regards,

gordom

 

[Pete_UK]

Assuming your data occupies columns A and B and starts on row 2 (with
header in row 1), you can put a header in C1 and this formula
(temporarily) in C2:

=IF(A2="","",B2&IF(B3="","",", "&B3)&IF(OR(B3="",B4=""),"",IF
(B4="","",", "&B4))&IF(OR(B3="",B4=""),"",IF(B5="","",", "&B5))&IF(OR
(B3="",B4="",B5=""),"",IF(B6="","",", "&B6)))

Then you can copy this down as far as you need. It will give you
something like this:

NAME DESC Multi_Desc
nameA desc1 desc1, desc2, desc3, desc4
desc2
desc3
desc4

nameB desc1 desc1, desc2
desc2

nameC desc1 desc1, desc2, desc3
desc2
desc3

nameD desc1 desc1, desc2, desc3, desc4, desc5
desc2
desc3
desc4
desc5

and so on. It will cope with up to 5 descriptions for each name.

Then you should highlight column C, click <copy>, then Edit | Paste
Special | Values (check) | OK and <Enter>, in order to fix the values.
Click in C2 and then on Data | Filter | Autofilter, then using the
filter pull-down on cell A1 you should select Blanks (you may need to
scroll down to see this option, depending on how many names you have).
Then highlight all the visible rows from row3 downwards, and click on
Edit | Delete Row. Then select All from the filter pull-down on cell
A1.

Finally, you can delete column B, to end up with what you want.

Hope this helps.

Pete

 

[Sheeloo]

Another option is as follows;
Assuming you have your data in Col A & B with no header rows

1. In C1 enter =A1 and in D1 enter =B1
2. In C2 enter
=IF(A2="",C1,A2)
and copy down
3. in D2 enter
=IF(A2="",IF(B2="",D1,D1&","&B2),B2)
and copy down
4. Copy Col C&D and Paste Special as values on itself
5. Filter on Col B not equal to Blank and delte those rows
6. Clear filter