Excel: Concatenateif
Say you have a table that you would like to summarise. Something like this:
Is there an easy way to compute the yellow Rostered On column?
In Excel 2019 and above there are several ways, and they all use the new and very useful function TEXTJOIN. Earlier versions require a macro (see the end).
The basic method that works is a formula like the following:
=TEXTJOIN(", ", TRUE, IF(C3:F3="yes",Person,""))
where Person are the column headings that you want to add, and C3:F3 are the cells that contain "yes". In this case we are computing the "Monday" row.
NOTE: This is an array formula, so you need to type CTRL-SHIFT-ENTER at the end to complete it.
What if you have more than just "yes", such as "maybe", and you want something like this:
Just replace the last "" (i.e. the elseif condition) with an additional nested if statement:
=TEXTJOIN(", ", TRUE, IF(C3:F3="yes",person,
IF(C3:F3="maybe",person & "(maybe)","")
))
You can continue these nested ifs for even more conditions:
=TEXTJOIN(", ", TRUE,
IF(C3:F3="yes",
person,
IF(C3:F3="maybe",
person & "(maybe)",
IF(C3:F3="morning",
person & "(morning)",
""
)
)
)
)
Excel 365
With Excel 365, they have introduced a new function "Array Formula" which means you don't need to type CTRL-SHIFT-ENTER. This makes the code a lot easier to read, and clears up which part of the formula actually invokes arrays.
See https://stackoverflow.com/questions/45685719/textjoin-only-when-the-condition-is-met
=TEXTJOIN(", ", TRUE, arrayformula(IF(C3:F3="yes",Person,"")))
Excel 365 also has the new filter function that makes it even easier:
=TEXTJOIN(", ", true, FILTER(person, C3:F3 = "yes"))
Macro Method (for Excel versions before 2019)
Stack Overflow is always useful. Just look at this cool excel macro
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.
=ConcatenateIf(B2:E2,"yes",$B$1:$E$1,", ")
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = vbLf) 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
http://stackoverflow.com/questions/22639868/vba-user-defined-function-for-concatenate-if-by-rows
To run a User Defined Function, create a module in Personal.xlsb. Then call this function in the following dialog box: Insert Function > Or select a category: User Defined
