Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to combine several successive rows into one cell
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to combine several successive rows into one cell
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 On Jan 6, 12:25*am, gordom wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to combine several successive rows into one cell
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 "Pete_UK" wrote: 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 On Jan 6, 12:25 am, gordom wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy row on 2+ w'ksheets and paste to successive rows on new w/sh | Excel Discussion (Misc queries) | |||
I am not able to extend formulas to successive rows | Excel Worksheet Functions | |||
Combining Totals on Successive Rows Based on Two Criterion | Excel Discussion (Misc queries) | |||
formula to apply concatenation to successive rows of data | Excel Worksheet Functions | |||
combine rows into one cell separated by comma | Excel Discussion (Misc queries) |