How To Conditionally Concatenate A Range

2016-05-21

VBA

Excel has some great built in functions for summing and counting conditionally based on given criteria, but to concatenate a range conditionally we will need to create our own user defined function.

This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice.

Function ConcatenateIf(CriteriaRange As Range, Criteria As Variant, _
    ConcatenateRange As Range, Optional Delimiter As String = ",") As Variant

    Dim j As Long
    Dim TempString As String
    TempString = ""

    On Error GoTo ErrorGoTo
    'Check if criteria range and concatenate range are the same size
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If

    'Loop through cells in the criteria range
    For j = 1 To CriteriaRange.Count
        'Add item to the string if criteria is met
        If CriteriaRange.Cells(j).Value = Criteria Then
            TempString = TempString & Delimiter & ConcatenateRange.Cells(j).Value
        End If
    Next j

    'Remove starting delimiter
    If TempString <> "" Then
        TempString = Mid(TempString, Len(Delimiter) + 1)
    End If

    ConcatenateIf = TempString
    Exit Function
ErrorGoTo:
ConcatenateIf = CVErr(xlErrValue)

End Function

Example

In this example we have a list of email addresses with a Y or N in the next column depending on if the person is planning to attend the national dinosaur convention. Now we want to get a comma separated list of the email addresses for those who plan to attend.

We use the formula:

=ConcatenateIf(C2:C10,B12,B2:B10, ", ")

ConcatenateIf

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This