0

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!

tryingtolearn
  • 1
  • 1

1 Answers1

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81