Help writing a CONCATENATEIF function (filtered concatenation overrange)

[felciano]

Hi --

I am trying to create a conditional concatenation function, similar to
Excel's built-in CONCATENATE function except (a) it works on ranges
and (b) you can provide a second range of values to test against and
select the values to use form the first range. This is similar to
COUNTIF, where you only count cells that match a certain criterion.

I've found some examples of doing range-based concatenation on the web
(e.g. http://www.cpearson.com/excel/stringconcatenation.aspx) but I
can't figure out how to do the secondary range-based criterion
checking. Basically I need to be able to pass a criterion into the
function such as ">5", walk the two ranges in parallel, and have that
test be applied to the current cell in the second range. AFAIK Excel
doesn't have an Eval function that would enable me to do this.

Can someone suggest a strategy or sample code to get me started on
this?

Thanks in advance for your time!

Ramon

 

[OssieMac]

Hello Ramon,

I am not sure that I understand your question. However, when you want to put
ranges together in VBA you use the Union function. Perhaps the following
example might point you in the right direction.

Dim rng1 As Range
Dim rng2 As Range
Dim rngCombined As Range

Set rng1 = ActiveSheet.Range("A1:A10")

Set rng2 = ActiveSheet.Range("C1:C10")

'Combine the 2 ranges
Set rngCombined = Union(rng1, rng2)

'Add a third range to the already combined range
Set rngCombined = Union(rngCombined, ActiveSheet.Range("F1:F10"))

 

[Charabeuh]

Something like that ?

This function can be called by
=Conca_If(A1:A7, "<=""abc""")
it will concatenane each cell of A1:A7 only if
the cell is <="abc"
or
=Conca_If(A1:A7, "<=""abc""",D17)
it will concatenane each cell of D17 only
if the corresponding cell in A1:A7 is <="abc"
or
if B1 contains <="abc"
=Conca_If(A1:A7, B1,D17)

------------------------------------------
Option Explicit

Public Function Concat_If(xSource As Range, xCrit As String, Optional xItem
As Range) As String
Dim xCell As Range, xOffset As Long

Concat_If = ""
xCrit = " " & xCrit
If Not xItem Is Nothing Then
xOffset = xItem.Column - xSource.Column
Else
xOffset = 0
End If

For Each xCell In xSource
If Application.Evaluate(xCell.Address & " " & xCrit) Then
Concat_If = Concat_If & xCell.Offset(0, xOffset)
End If
Next xCell

End Function

 

[Charabeuh]

NB:

I have presumed that the values to compare (A1:A7) are strings.
If these values are numbers, the function seems to be ok but you
must replace the criteria "<=""abc""" with "<5" or put <5 in B1.

 

[felciano]

NB:

I have presumed that the values to compare (A1:A7) are strings.
If these values are numbers, the function seems to be ok but you
must replace the criteria "<=""abc""" with "<5" or put <5 in B1.

Fantastic! That is exactly what I needed -- thank you!

Ramon

 

[felciano]

NB:

I have presumed that the values to compare (A1:A7) are strings.
If these values are numbers, the function seems to be ok but you
must replace the criteria "<=""abc""" with "<5" or put <5 in B1.

One more question: it looks like this function will not get
recalculated automatically. For example, if I change the values in one
of the ranges, the concatenated string does not update automatically.
Is there a way to flag the function to be re-calculated in the same
way that SUMIF and COUNTIF are handled?

Thanks,

Ramon

 

[Charabeuh]

Hi

Insert the line 'Application.volatile' at the beginning of the code.

-----------------------------------------
Option Explicit

Public Function Concat_If(xSource As Range, xCrit As String, Optional xItem
As Range) As String
Dim xCell As Range, xOffset As Long

Application.volatile
Concat_If = ""
.....




"felciano" <[email protected]> a écrit dans le message de

NB:

I have presumed that the values to compare (A1:A7) are strings.
If these values are numbers, the function seems to be ok but you
must replace the criteria "<=""abc""" with "<5" or put <5 in B1.

One more question: it looks like this function will not get
recalculated automatically. For example, if I change the values in one
of the ranges, the concatenated string does not update automatically.
Is there a way to flag the function to be re-calculated in the same
way that SUMIF and COUNTIF are handled?

Thanks,

Ramon