How to concatenate values in multiple cells based on a condition?

Question

I have a need to search a row of cells, and for every cell that contains a specific value, return the value from the cell above.

For example, consider the following

+---+--------+--------+--------+--------+--------+----------+
|   |   A    |   B    |   C    |   D    |   E    |     F    |
+---+--------+--------+--------+--------+--------+----------+
| 1 |   UK   |   DE   |   FR   |   HK   |   TW   |          |
+---+--------+--------+--------+--------+--------+----------+
| 2 |   YES  |        |   YES  |   YES  |        |          |
+---+--------+--------+--------+--------+--------+----------+
| 3 |        |   YES  |        |   YES  |   YES  |          |
+---+--------+--------+--------+--------+--------+----------+
| 4 |   YES  |        |        |   YES  |        |          |
+---+--------+--------+--------+--------+--------+----------+

So I want to insert a formula into cells F2, F3 and F4 which will give the following results

F2 = UK,FR,HK
F3 = DE,HK,TW
F4 = UK,HK

Can this be done?

Thanks

Answer 1

I have found a simple, scalable solution that uses an array formula to concatenate multiple cells that satisfy a certain condition.

Applied to your example, paste into cell F2:

=TEXTJOIN(",", TRUE, IF(B3:F3 = "YES", B$2:F$2, ""))

and hit ctrl+shift+enter to enter as an array formula, and copy over cells F3--F4.

The reason why this works is left as an exercise to the reader. It's fairly clear, but I prefer "magic".

I hope this helps anyone with a similar problem.

Answer 2

Write your own UDF

Original solution.

Excerpt from the article

1. Open VBA Editor by clicking Visual Basic on Developer tab or using Alt+F11 combination
2. Create new module by right clicking Microsoft Excel Objects in the top left corner and choosing Insert->Module from the context menu.
3. Insert the following code

UDF:

Function ConcatenateIf(CriteriaRange As Range, _
                       Condition As Variant, _
                       ConcatenateRange As Range, _
                       Optional Separator As String = ",") As Variant
'Update 20150414
Dim xResult As String
On Error Resume Next
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
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Later on you can use it, if you enable macro in your workbook.
In your specific example write the following formula into F2 cell and copy over needed range.

=ConcatenateIf($A2:$E2,"YES",$A$1:$E$1,",")