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

Concatenate Comments For Rows Above Each Record Id

Rate this: (3/5 from 1 vote)

	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 Michaniker on 30 Sep 2009

Replies - Displaying 1 to 2 of 2

Order Replies By: Most Recent | Chronological | Highest Rated

	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 Excel Helper on 30 Sep 2009

	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 Michaniker on 01 Oct 2009

Displaying page 1 of 1