Merge Cells into One Cell based on Contents of Another Column

[dcbuzzell]

Hello All - Excel 2010 issues. I need to combine multiple cells in Column B that represent one to three descriptive lines for the data in Column A. I would like the contents of the descriptive lines separated by a space, merged into the first cell and the duplicative rows deleted.

Excel 2010
	A	B
1	MODEL	DESCRIPTION
2	abc1234	Part 1 Line 1
3		Part 1 Line 2
4		Part 1 Line 3
5	abc5678	Part 2 Line 1
6		Part 2 Line 2
7		Part 2 Line 3
8	abc9012	Part 3 Line 1
9		Part 3 Line2
10		Part 3 Line 3
11	bcd1234	Part 4 Line 1
12		Part 4 Line2
13	bcd4567	Part 5 Line 1
14	bcd8901	Part 6 Line 1
15		Part 6 Line 2
16		Part 6 Line 3
17	cde1234	Part 7 Line 1
18		Part 7 Line 2
19	cde5678	Part 8 Line 1
Sheet2

In this example, Cells B2:B4 would be combined into B2, separated by a space; Rows 3 and 4 would be deleted. Then cells B5:B7 would be combined in B5, and Rows 6 and 7 deleted. Continuing down in similar fashion, Row 13 would be unchanged.

Thanks in advance for any help.

 

[Michael M]

While the request is doable, why would you want to combine data into one cell ?
It will make it very difficult to do anything else with it once the "merge" has been done !

 

[Michael M]

Darn, duplicate !!

 

[dcbuzzell]

Thank you for your reply.

The data should be combined, but is separated into (up to) three cells by the arcane system it's coming from - combined, they are the long description for the data in Column A. In order to sort and analyze the data, I need the data in column B merged as I mentioned.

 

[Michael M]

OK, try this

Sub MM2()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Range("A" & r).Value = "" Then
        Range("B" & r - 1).Value = Range("B" & r - 1).Value & " " & Range("B" & r).Value
        Rows(r).Delete
    End If
Next r
End Sub

 

[meijie]

Hi Michael,

What I need is exactly inverse to him.
The content in my column B is all in one cell, and devided by space.
I can let the column B split into separate cells, but the content in column A will not match with the column B anymore.
Do you have some suggestions?

Thanks in advance!