Is there a formula for conditional concatenating?
 

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

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

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

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

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

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

"JulieD" wrote:

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

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" wrote:

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

glad it worked.

"Erny Meyer" wrote in message
...
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" wrote:

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

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

"JulieD" wrote in message
...
glad it worked.

"Erny Meyer" wrote in message
...
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" wrote:

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

Is there a formula for conditional concatenating?
 

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

Thanks,
Wanda

"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

Is there a formula for conditional concatenating?
 

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

"Erny Meyer" wrote:

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

Is there a formula for conditional concatenating?
 

Julie, Harold Staff,

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

(The best solution was found he
http://www.officekb.com/Uwe/Forum.as...-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
-------


"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