Macro to concatenate text strings

Hi - I am trying to concatatenate text strings from one sheet to another. The raw data sheet consists of Names of people and Comments made by each person.
I want to concatenate each comment made by each person and produce the results in a new sheet. My list of names and their comments would be a dynamic list - that is - each time a person enters a new comment - a new row will be created with the persons name and his comment.
my raw data would be:

Find the example in the attached image..
pic.JPG

Note that in the summary tab - any "(blank)" comments will be ignored..and only the non blanks will be picked..the names in the summary tab are pre-populated i.e. they are standard names and will not be dynamic. its a fixed list of names. only Tom, Harry, PEter and Rob will be on this list. On an on going basis - their comments will be updated based on what we see in Sheet1(raw data). We do not need to re-generate these names from the first sheet. i will do that already.

The macro will run through Sheet1 (raw data) and will look look at each comment the person has made and then concatenate it into sheet 2 - where the names of the person already exsist. the macro will only output results in "Comments" column of the Sheet 2!

Can you please help me create something of this sort??

I am using excel 2003

Please attach an excel file instead of a jpg. It makes it much easier for us to provide you a solution.

Do you know how to use a user-defined function?

i tried to create one..i am working on it actually but its a longer approach..
i created this function:

Function ConcatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & " ; "
Next
ConcatRange = Left(sbuf, Len(sbuf) - 1)

end function

i was thinking of first creating columns of each name in the Raw sheet i.e. append columns Tom, Harry, etc..
and first copy their respective comments under their columns..that means in Toms column - i will copy toms comment if its non blank..

Name comment Tom Harry
Tom 123 123
harry xcbv xcbv
peter
tom 332 332


and then i could call the concatrange function that i have created. it works fine but i see extra delimiters i.e. ";" after each comment. if i could remove these for non blank cells then i could concatenate them all..
does this make sense? i know its a longer approach!!! i coudlnt figure out a decent macro so tried the function instead..

      ---A---- ----------B---------- C ---D--- -------------------E-------------------
  1   Raw Data                         Summary                                        
  2   Name     Comments                Name    Comments                               
  3   Tom      Amazing!!               Peter   I don't know anything                  
  4   Harry    I knew it..             Harry   I knew it..                            
  5   Peter                            Rob                                            
  6   Tom      Where are you?          Tom     Amazing!!; Where are you?; I am outside
  7   Tom      I am outside                                                           
  8   Peter    I don't know anything                                                  
  9   Rob

The formula in E2 and copied down, which MUST be confirmed with Ctrl+Shift+Enter rather than just Enter, is

=CatIf(($A$3:$A$9=D3)*($B$3:$B$9<>""), $B$3:$B$9, "; ")

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",") As String
    ' shg 2007
    ' Catenates the elements of vInp separated by sSep

    Dim iRow        As Long
    Dim iCol        As Long
    Dim i           As Long

    On Error Resume Next
    i = UBound(avbIf, 2)

    If Err.Number Then
        ' avbIf is 1D
        For iRow = 1 To rInp.Rows.Count
            For iCol = 1 To rInp.Columns.Count
            i = i + 1
                If avbIf(i) Then CatIf = CatIf & rInp(iRow, iCol) & sSep
            Next iCol
        Next iRow
    Else
        ' it's 2D
        For iRow = 1 To rInp.Rows.Count
            For iCol = 1 To rInp.Columns.Count
                If avbIf(iRow, iCol) Then CatIf = CatIf & rInp(iRow, iCol) & sSep
            Next iCol
        Next iRow
    End If
    
    If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

Just reference the ranges you need.

Extend the ranges further down than you need.