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

Solution 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.

Solution 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,",")

Solution 3

In F2 copy-paste this formula:

=CONCATENATE(IF($A2="YES",A$1&",",),IF($B2="YES",B$1&",",),IF($C2="YES",C$1&",",),IF($D2="YES",D$1&",",),IF($E2="YES",E$1&",",))

and drag down against the column.

Explanation:

IF($A2="YES",A$1&",",)
IF($B2="YES",B$1&",",)
IF($C2="YES",C$1&",",)
IF($D2="YES",D$1&",",)
IF($E2="YES",E$1&",",)

The above code has been rewritten 5 times and the column name has been changed. It checks whether the cell in the current row has a 'YES'. If it does then it'll enter the header of the column which is 'A$1'. Notice that $1 is absolute reference to the first row i.e. the header.

In the end, I have encapsulated all the five IF statements using CONCATENATE statement.

Hope this helps.

Comments

[Typhoon101]

Thanks Zeeshan. That works great, but i was wondering if there was a more easier to manage solution - maybe using array formulas. I never thought of mentioning it in my original post, but my spreadsheet has a couple of hundred columns. That means a couple of hundred IF statements. If I add or remove a column at any time (something that will happen fairly frequently), I will need to keep updating all the IF statements.

[Zeeshan S.]

I'll check and get back to you.

[Alex Jorgenson]

Note that TEXTJOIN is only available in Excel 2016 and Office 365.