3

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.

vbastrangledpython
  • 93
  • 1
  • 2
  • 7
  • 1
    Would help to add your attempted code to your question - even if it doesn't quite work. – Tim Williams Mar 25 '14 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 Mar 25 '14 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 Mar 25 '14 at 16:15
  • Posting a solution now.... – John Bustos Mar 25 '14 at 16:16
  • [TEXTJOIN for xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2013-with-criteria/50719050#50719050) –  Jun 08 '18 at 00:17

1 Answers1

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

John Bustos
  • 19,036
  • 17
  • 89
  • 151