I have a simple Google Sheets spreadsheet that tracks which task a group of people have accomplished. As each person accomplishes a task, I use a simple checkbox to mark completion:
Task: A B C D
Human 1 [x] [] [] []
Human 2 [x] [x] [x] []
Human 3 [] [x] [] [x]
Human 4 [x] [x] [x] [x]
I'm looking for some way to have a final column that would summarize which tasks have NOT yet been completed, e.g:
Task: A B C D Not Yet Completed:
Human 1 [x] [] [] [] B, C, D
Human 2 [x] [x] [x] [] D
Human 3 [] [x] [] [x] A, C
Human 4 [x] [x] [x] [x] (Blank)
My goal is to do this using a single formula that will run through each row and concatenate the column names for the boxes that are unchecked.
I've tried using ARRAYFORMULA in conjunction with TEXTJOIN like so:
=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(B2:E2=FALSE,$B$1:$E$1,"")))
This does what I want, but requires that I copy that formula to EVERY row I want to track.
When I try to apply the text join to a 2-dimensional range, it will join ALL of the text into a single cell, rather than applying it to each row one by one:
=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(B2:E5=FALSE,$B$1:$E$1,"")))
---Result---
Task: A B C D Not Yet Completed:
Human 1 [x] [] [] [] B, C, D, D, A, C
Human 2 [x] [x] [x] []
Human 3 [] [x] [] [x]
Human 4 [x] [x] [x] [x]
By contrast, when I set up a custom formula in Conditional Formatting, it allows me to "Apply to Range" (i.e. over multiple rows), but then enter a formula that is limited to a single row. It then applies this formula over each row in the range one by one.
is there any way to do this same "Apply to Range" concept within a cell using ARRAYFORMULA or other functions that are native to Google Sheets?
