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

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

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

Hi again Julie

What I never remember to set up in test scenarios is "nothing". Some day
I'll learn. This is not good:
Application.WorksheetFunction.CountA(ConcCheck)
-clear a cell and the function returns nothing. Let us instead do

i = ConcCheck.Count
j = ConcRange.Count

Best wishes Harald

"JulieD" skrev i melding
...
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

Hi Harald

didn't think of testing it with nothing in it either :)

so final(?!?!) function

Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String
'written by JulieD and Harald Staff
'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 = 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

-------
now all i need to do is update my website and put it on there - unless you
have a better home for it?

Cheers
JulieD


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

What I never remember to set up in test scenarios is "nothing". Some day
I'll learn. This is not good:
Application.WorksheetFunction.CountA(ConcCheck)
-clear a cell and the function returns nothing. Let us instead do

i = ConcCheck.Count
j = ConcRange.Count

Best wishes Harald

---snip---

Fine. Final until we find another bug.

I don't have a home for it yet, so put it onto your website, along with my
initial Concat formula -they deserve to be together.

Best wishes Harald

"JulieD" skrev i melding
...
Hi Harald

didn't think of testing it with nothing in it either :)

so final(?!?!) function

Public Function CONCAT_IF(ConcCheck As Range, _
(...)
-------
now all i need to do is update my website and put it on there - unless you
have a better home for it?

Hi Harald

with more testing i've discovered that you need to use named ranges to
access ranges on other worksheets ... but i've just added that into the
comments :)

i've uploaded both the CONCAT_RANGE and CONCAT_IF functions to my web site
with brief notes on using - check it out at
www.hcts.net.au/tipsandtricks.htm

and i've decided that i'm going to lose the purple in the next version of my
site!

Cheers
JulieD


"Harald Staff" wrote in message
...
Fine. Final until we find another bug.

I don't have a home for it yet, so put it onto your website, along with my
initial Concat formula -they deserve to be together.

Best wishes Harald

"JulieD" skrev i melding
...
Hi Harald

didn't think of testing it with nothing in it either :)

so final(?!?!) function

Public Function CONCAT_IF(ConcCheck As Range, _
(...)
-------
now all i need to do is update my website and put it on there - unless
you
have a better home for it?