Home > Forum Home > Developing and Auditing Analytical Models > Concatenate Comments for Rows Above Each Record ID Share

Concatenate Comments for Rows Above Each Record ID

Excel Help for Concatenate Comments For Rows Above Each Record Id in Developing and Auditing Analytical Models


Forum TopicPost Reply Login

Concatenate Comments For Rows Above Each Record Id

Rate this:
(3/5 from 1 vote)
Confused  Unfortunately my strengths do not lie in VBA for Excel.  But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often.  I have three columns (Record ID, Comment, Results).  The Results column is added to capture concatenation of the Comments column.  Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id.  This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results.  Can anyone help?

Record ID       Comment                                       Results
                        I am a fish.
1                      I am a dolphin.                              I am a fish.I am a dolphin.
2                      I like gravy.                                   I like gravy              
                        Blue sky.
                        Red shirt.
                        Green grass.
3                      Yellow sun.                                   Blue sky.Red shirt.Green grass.Yellow sun.
                        Little pink
4                      houses for you and me.                  Little pink houses for you and me.                                  
 Michaniker
 Posted by on
 
Replies - Displaying 1 to 2 of 2Order Replies By: Most recent | Chronological | Highest Rated
Happy
Solved!!! You're a Genius!
Rate this:
(3/5 from 1 vote)
Thank you so much. It worked perfectly. I'm so thrilled!
 Michaniker
 Posted by on
Happy
Concatenate Comments VBA code
Rate this:
(3/5 from 1 vote)
Assuming that you have the Record ID in column A, the Comment in column B and the Results in column C, the following VBA macro will work. I have tested it.

VBA Code:
Sub Concatenate_Comments()
Application.ScreenUpdating = False
Dim strResult As String
Dim strComment As Variant
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
For Each strComment In Selection
    'If not ID
    If Trim(strComment.Offset(0, -1).Text) = "" Then
        'Build onto Result
        strResult = strResult & " " & strComment
        'Put nothing in the Result
        strComment.Offset(0, 1).Formula = ""
    'Else there is an ID
    Else
        'Build onto Result
        strResult = strResult & " " & strComment
        'Put the Result
        strComment.Offset(0, 1).Formula = strResult
        'Release the Result for the next one
        strResult = ""
    End If
Next
Application.ScreenUpdating = True
End Sub

Enjoy!
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Concatenate Comments for Rows Above Each Record ID in the

© Business Spreadsheets   Legal   Follow us: