CODEWITHSUNDEEP
google-sheets
emmby
emmby

Reputation: 100464

How do I collect the column headers that have non-empty cells for each row?

I have a table that lists who is working on what, with numbers to indicate how long they think it will take (the other cells are left blank).

       | Alex | Betty | Charlie |
---------------------------------
Task A |   1  |   2   |         |
Task B |      |       |    2    |
Task C |   1  |       |    3    |

I would like to generate a column called "Owners" that is a comma-separated list of who is working on each task, based on the non-empty values in the Alex, Betty, and Charlie columns. For example, Task A is being worked on by Alex and Betty, so the value in "Owners" for that row would be "Alex, Betty".

       | Alex | Betty | Charlie | Owners
------------------------------------------------
Task A |   1  |   2   |         | Alex, Betty
Task B |      |       |    2    | Charlie
Task C |   1  |       |    3    | Alex, Charlie

What's the best way to do this in Google Sheets? Note that my headers may not be in row 1.

Upvotes: 2

Views: 372

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:D="",,B1:D1&",")),,9^9))), ",$", ))

enter image description here

Upvotes: 1

Related Questions