VBA User Defined Function for "Concatenate If" by rows

Question

I have some data of whether or not a particular "service" (e.g. Bird Survey) has been performed for a particular site, with a "yes" or "no" for each service.

E.G.

Column A contains site names, say A, B, C, D and E with the title "Site Name" in A1 then "A" in A2 etc.
Column B contains "Bird Survey" in B1, then either a "yes" or "no" for B2-B6.
Ditto for other services in columns C, D and E, lets say "Bat Survey", "LVI" and "Land Registry" respectively.
In F I want to concatenate the service names for each row containing a "yes". E.G. lets say the values for B2,C2,D2 and E2 are "yes", "yes", "no" and "yes", I want F2 to contain Bird Survey, Bat Survey.

As I understand it there are no native functions in excel that can do this, and so I've been trying to create a user defined function in VBA. I've tried two approaches

one based on feeding two ranges (column names and row of "yes/no"'s) into the UDF and then combining these into an array to apply some sort of lookup criteria
and another returning column letter from one the yes/no range only then selecting from the column names based on column letter.

I've not been able to get either to work though. Note that in the end I need to create a UDF that works for a varying number of services, they won't be pre-defined as in this example.

Any suggestions?

Many thanks in advance.

Would help to add your attempted code to your question - even if it doesn't quite work. — Tim Williams
– Tim Williams, Commented Mar 25, 2014 at 16:07
A few questions... (1) Given your example " E.G. lets say the values for B2,C2,D2 and E2 are "yes", "yes", "no" and "yes", I want F2 to contain Bird Survey, Bat Survey." - Should that say "Bird Survey, Bat Survey, Land Registry"? (2) Are you limited to "yes" / "no" or can it be (1 / 0) or (True / False)? — John Bustos
– John Bustos, Commented Mar 25, 2014 at 16:07
Woops, sorry, yes you're right it should indeed say "Bird Survey, Bat Survey, Land Registry". It could also be changed to a different binary condition, (1/0) or (True/False) as you suggest. — vbastrangledpython
– vbastrangledpython, Commented Mar 25, 2014 at 16:15

John Bustos · Accepted Answer · 2014-03-25 16:18:12Z

9

Based upon what you're looking for, I found this function a long time ago and it's worked a charm:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Given your question, it would be used as follows:

=ConcatenateIf(B2:E2,"yes",$B$1:$E$1,", ")

Initial credit goes to this link.

Hope this does the trick!!

answered Mar 25, 2014 at 16:18

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitry Pavliv Over a year ago

nice answer and congrats on 5k:)

Collectives™ on Stack Overflow

VBA User Defined Function for "Concatenate If" by rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related