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
|