Is there a formula for conditional concatenating?

[Guest]

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

 

[JulieD]

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("D1100")
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 D1100 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

 

[Guest]

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]

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

 

[JulieD]

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

 

[Guest]

Thanks Julie & Carey,

I looked at this and it is very useful; I may slightly modify the function
to include the criteria checking range (which is in my case a different one
from the concatenation range), so maybe 4 parameters instead of 3, but it
will solve my problem.

Cheers,
Erny

 

[Guest]

Hi again,
Correction to my last one (I had not seen your 2nd reply yet): this makes it
even easier. I tried it and it worked.

Thanks again and ciaooo,
Erny

 

[JulieD]

glad it worked.

Hi again,
Correction to my last one (I had not seen your 2nd reply yet): this makes
it
even easier. I tried it and it worked.

Thanks again and ciaooo,
Erny

 

[JulieD]

btw i have another version which allows both text and numbers as the
criteria ... if you'ld like it email me direct at julied_ng at hcts dot net
dot au

Cheers

 

[Guest]

I think is the answer I am searching for. Not sure how to implement it. Can
someone help me?

Thanks,
Wanda

 

[gsedwardsee]

I also pine for the day when the concatenateif function was available.

 

[gsedwardsee]

Julie, Harold Staff,

Thanks for your efforts! The following version looks like your best version
yet.

(The best solution was found here:
http://www.officekb.com/Uwe/Forum.aspx/excel-prog/53163/New-Function-ConcatenateIF)

---------
Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String
'
'Version Posted: 01 Apr 2005 01:19 GMT
'Written by JulieD and Harald Staff
'
'ConcCheck - range to check for the criteria
'ConcCrit - the criteria
'ConcRange - range to concatenation
'DelimitWith - the delimination character(s)

Dim Cel As Range
Dim i As Long, j As Long
Dim checkarray() As String
Dim rangearray() As String

If ConcRange Is Nothing Then Set ConcRange = ConcCheck

i = ConcCheck.Count
j = ConcRange.Count

If i <> j Then
Exit Function
End If

ReDim checkarray(i - 1)
ReDim rangearray(i - 1)

i = 0
For Each Cel In ConcCheck
checkarray(i) = Cel.Text
i = i + 1
Next
i = 0
For Each Cel In ConcRange
rangearray(i) = Cel.Text
i = i + 1
Next

For i = 0 To j - 1
If checkarray(i) = ConcCrit Then CONCAT_IF = _
CONCAT_IF & rangearray(i) & DelimitWith
Next
If CONCAT_IF <> "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith))
End Function