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.