Hi Harald

thanks for this - good idea on the ConcRange and yes, dimming a variable
would be considered "good programming"

Cheers
JulieD

"Harald Staff" wrote in message
...
Hi Julie

It's beautiful ! Thanks for sharing this.

Two minor adjustments only, first is
Dim j as Long
;-)
Then I'd make ConcRange optional, so that it behaves the same way as
Sumif.

Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String

'ConcCheck - range to check for the criteria
'ConcRange - range to concatenation
'ConcCrit - the criteria
'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 = Application.WorksheetFunction.CountA(ConcCheck)
j = Application.WorksheetFunction.CountA(ConcRange)
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

Best wishes Harald


"JulieD" skrev i melding
...
Hi Harald

following on from our previous conversation would you like to test the
following:

---------
Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range,
ConcCrit
As Variant, _
Optional DelimitWith As String) As String

'ConcCheck - range to check for the criteria
'ConcRange - range to concatenation
'ConcCrit - the criteria
'DelimitWith - the delimination character(s)

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


i = Application.WorksheetFunction.CountA(ConcCheck)
j = Application.WorksheetFunction.CountA(ConcRange)
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
-----------

Cheers
JulieD