HARALD STAFF - CONCAT_IF

[JulieD]

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

 

[Harald Staff]

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]

Hi Harald

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

Cheers
JulieD

 

[Harald Staff]

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]

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

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---

 

[Harald Staff]

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]

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

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