ksawyers wrote...
I am searching for a formula that will combine all the cells within a range
based on matching other criteria. For instance, I want to concatenate all of
the comments entered for a particular name within a list of 5000 names where
the name is repeated numerous times. Can anyone help? Thanks!
Why are you doing something like this in Excel? It's not the best tool
for text processing (not by a long shot).
If you insist on using Excel for this, then you'll have to use VBA.
Excel provides *NO* built-in means of concatenating arbitrary
collections of text strings. Both the concatenation operator, &, and
the CONCATENATE function provide only pair-wise functionality.
One possible udf (user-defined function) is mcat given in
http://groups.google.com/group/micr...tions/msg/94456a9e326b19a6?dmode=source&hl=en
(or
http://makeashorterlink.com/?S1E33459B ). With it you could use an
array formula like
=TRIM(mcat(IF(IDs=SpecificID,Comments,"")&" "))
to concatenate all comments from the range or array designated by
Comments corresponding to all IDs from the range or array designated by
IDs that match the ID given by SpecificID. This also inserts a single
space between each of the comments. Inserting a newline between each of
the comments is a bit trickier.
=SUBSTITUTE(SUBSTITUTE(TRIM(mcat(IF($C$1:$C$20=C22,
SUBSTITUTE(D1
20," ","<s>"),"")&" "))," ",CHAR(10)),"<s>"," ")
which assumes the literal substring <s> doesn't appear in your
comments. If it could, use some other string (perhaps CHAR(127)) as a
temprary placeholder for space characters in the original comments.
