Is there a way to automate populating cells with the names of the column depending on whether the row has values?

Question

I'm tasked with populating cells in excel with values that are names of the columns. For example here's the type of table I'm trying to accomplish. I need to populating the values of column A with the names of the columns that have values (an "x") in that specific row.

+---+-----------+----------+----------+-----------------+
|   | A         |  B       | C        | D               |
+---+-----------+----------+----------+-----------------+
| 1 |           | name1    | name2    | name3           |
+---+-----------+----------+----------+-----------------+
| 2 |name2;name4|    x     |          |      x          |
----+-----------+----------+----------+-----------------+
| 3 |name3;name4|          |   x      |      x          |
+---+-----------+----------+----------+-----------------+
| 4 |name1;name2|    x     |   x      |                 |
+---+-----------+----------+----------+-----------------+
| 5 |           |    x     |   x      |      x          |
+---+-----------+----------+----------+-----------------+
| 6 |           |          |          |                 | 
+---+-----------+----------+----------+-----------------+

For example, A5 should have the value name1;name2;name3, because columns B, C and D have values. A6 should not have anything.

Is there a way to automate this in Excel? Or do I just have to keep doing it manually?

Thank you!

Answer 1

With Office 365 and later:

=TEXTJOIN(";",TRUE,FILTER($B$1:$D$1,B2:D2="x",""))

With Excel 2019:

 =TEXTJOIN(";",TRUE,IF(B2:D2="x",$B$1:$D$1,""))

And use Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If one has an older version than those above they can use a UDF in vba. HERE is a UDF that mimics TEXTJOIN. The formula used would be the second one above.