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>

 

[craig.penny]

Try this

Sub ConCat()
Dim i As Long
Dim str As String
For i = 1 To ActiveSheet.UsedRange.Rows.Count + 1
      If i > 1 Then
            If Cells(i, 1) = Cells(i - 1, 1) Then
                  str = str & Cells(i, 2) & " "
            Else
                  Cells(i - 1, 3) = str
                  str = Cells(i, 2) & " "
            End If
      Else
            str = Cells(i, 2) & " "
      End If
Next i
End Sub

 

[cjwally4]

How would this look in the first cell of column c?

 

[craig.penny]

Can you explain what you want to do?

 

[cjwally4]

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

 

[craig.penny]

Sub ConCat()
Dim i As Long
Dim str As String
For i = 1 To ActiveSheet.UsedRange.Rows.Count + 1
      If i > 1 Then
            If Cells(i, 2) = "" And i < ActiveSheet.UsedRange.Rows.Count + 1 Then
                  Cells(i, 2) = Cells(i, 1)
                  Cells(i, 1) = Cells(i - 1, 1)
            End If
            If Cells(i, 1) = Cells(i - 1, 1) Then
                  str = str & Cells(i, 2) & " "
            Else
                  Cells(i - 1, 3) = str
                  str = Cells(i, 2) & " "
            End If
      Else
            str = Cells(i, 2) & " "
      End If
Next i
End Sub

Does that work?

 

[craig.penny]

And this one will start the comment strings at the top row and won't skip rows before adding the next one.

Sub ConCat()
Dim i As Long, j As Long
Dim str As String
For i = 1 To ActiveSheet.UsedRange.Rows.Count + 1
      If i > 1 Then
            If Cells(i, 2) = "" And i < ActiveSheet.UsedRange.Rows.Count + 1 Then
                  Cells(i, 2) = Cells(i, 1)
                  Cells(i, 1) = Cells(i - 1, 1)
            End If
            If Cells(i, 1) = Cells(i - 1, 1) Then
                  str = str & Cells(i, 2) & " "
            Else
                  j = j + 1
                  Cells(j, 3) = str
                  str = Cells(i, 2) & " "
            End If
      Else
            str = Cells(i, 2) & " "
      End If
Next i
End Sub

 

[cjwally4]

how would this be written in the spreadsheet formula

 

[VoG]

Try

Sub ccat()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 1 To lastrow
    If Range("A" & i).Value <> Range("A" & i + 1).Value Then
        iEnd = i
        Range("C" & iStart).Value = Join(Application.Transpose(Range(Cells(iStart, 2), Cells(iEnd, 2))), " ")
        iStart = iEnd + 1
    End If
Next i
End Sub

 

[craig.penny]

I assumed you were looking for a VBA solution.

Maybe VoG will enlighten us both.