Hi Erny

if you're going to give my UDF a go, the
ConcRange
in your case would be
A1:B5
the ConcCrit
would be either T1 or the cell reference holding T1
and the DelimitWith
would be a comma and/or space to separate Jim and Joe
- when i figure out how to put help into a UDF it will make life easier
Cheers
JulieD


"JulieD" wrote in message
...
Hi Erny

i don't think MS people come in here to read "hints" :)

Harald Staff and i were playing around with this idea - as another poster
raised it a few days ago - and i did a limited concat_if funciton
check out
http://tinyurl.com/5wzuz

for the discussion and the'function' ... you can put it in your
personal.xls file and then access it via the paste function wizard under
user defined functions.

Cheers
JulieD

"Erny Meyer" wrote in message
...
Thanks Julie, very much appreciate, actually I wished to avoid any
programming, as I will need to paste whatever formula I could find to a
larger range (lots of reference values and lots of names).

I was wondering whether something similar to the SUMIF formula for values
could existe such as a CONCATENATEIF for strings (maybe a hint to
microsoft
to add a functionality).

I will use your code as an option.

Kind regards,
Erny

"JulieD" wrote:

Hi Erny

no formula that i know of (someone else might know) but this code will
do it
for you
--
Sub concatvals()
Dim strvalue As String
Dim strsearch As String

For Each cell In Range("D1:D100")
If Not IsNull(cell.Value) Then
strsearch = cell.Value
strvalue = ""
For Each c In Range("A1:A100")
If c.Value = strsearch Then
If Len(strvalue) < 1 Then
strvalue = c.Offset(0, 1).Value
Else
strvalue = strvalue & ", " & c.Offset(0,
1).Value
End If
End If
Next
End If
cell.Offset(0, 1).Value = strvalue
Next
End Sub

---
where D1:D100 contains the unique references from column A
where E1:E100 is where the results can be put
where A1:A100 is your lists of codes
where B1:B100 is the list of people you want to concatenation in to
column E
next to the appropriate code id D

---
if you would like assistance implementing the code, please post back

Cheers
JulieD

"Erny Meyer" wrote in message
...
I would like to concatenate cells from a column based on the match in
the
preceding column with a defined value.

Example:
Col. A B
Row1: T1 Jim
Row2: T3 Paul
Row3: T1 Joe
Row4: T2 Mike
Row5: T3 Caroline

The formula should return for T1 for instance: Jim;Joe;
(for T2: Mike;
for T3: Paul;Caroline;)
Cells in column A can repeat any number of times.

Thanks in advance,
Erny