Combining Text

[cjwally4]

How can I combine the text in col B into one row for the matching identifier in column A?
<table border="0" cellpadding="0" cellspacing="0" width="160"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">COL A</td> <td style="width:72pt" width="96">COL B</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>Hello</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>my name</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>is john</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>how</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>are you</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>this</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S123</td> <td>fine day</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>Hello</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>my name</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>is john</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>how</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>are you</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">S465</td> <td>this</td> </tr> </tbody></table>

 

[cjwally4]

VoG, any suggestions on an excel formula? Thanks much

 

[VoG]

I assumed you were looking for a VBA solution.

Maybe VoG will enlighten us both.

Are you kidding I can't think of a (straightforward) formula solution.

 

[Rick Rothstein]

I am extracting comments associated with an order number from a TCL database. The format that extracts lists the order in column A and the comments in column B. The TCL format is multivalued meaning they can't be extracted in a single string. It looks as follows:

Order#1 Comment
Comment
Order#2 Comment
Comment
Comment

Etc. It's 1200 lines and the number of comments per order can bu up to 10. My first step is to fill in the blanks with the order number so each comment has a reference to the correct order number. What I want to do is take the comment string and put it all into one row:

Order#1 Comment, Comment
Order#2 Comment, Comment, Comment

Does that help?

THanks very much

First off, don't fill in the blanks... the VB code works better if you don't.:wink:

Sub ConcatenateCommentsPerOrderNumber()
  Dim LastRow As Long, Blank As Range, CommentRange As Range
  Const DataStartRow As Long = 2
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  With Cells(DataStartRow, "A").Resize(LastRow - DataStartRow + 1)
    For Each Blank In .SpecialCells(xlCellTypeBlanks).Areas
      Blank(1).Offset(-1, 1).Value = Join(WorksheetFunction.Transpose(Blank(1).Offset(-1, 1).Resize(Blank.Count + 1)), ", ")
    Next
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With
End Sub

 

[cjwally4]

I don't know VB in excel, hoping there is a formula i can write out in a cell?

 

[VoG]

Formula (yuk!)

Excel Workbook
	A	B	C
1	S123	Hello	Hello
2	S123	my name	Hello my name
3	S123	is john	Hello my name is john
4	S123	how	Hello my name is john how
5	S123	are you	Hello my name is john how are you
6	S123	this	Hello my name is john how are you this
7	S123	fine day	Hello my name is john how are you this fine day
8	S465	Hello	Hello
9	S465	my name	Hello my name
10	S465	is john	Hello my name is john
11	S465	how	Hello my name is john how
12	S465	are you	Hello my name is john how are you
13	S465	this	Hello my name is john how are you this
Sheet9