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:

Please Login or Register  to view this content.

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..

Please Login or Register  to view this content.

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, "; ")

Please Login or Register  to view this content.

Just reference the ranges you need.

Extend the ranges further down than you need.