'Concatenate cells based on criteria
So, I am trying to find the most effective way to concatenate some values to see what are the missing assignments that my students need to turn in. I have a table similar like this (the dates represent when the delivered the homework):
| ID | Name | Homework 1 | Homework 2 | Homework 3 |
|---|---|---|---|---|
| 1 | John | 09/07/21 | 09/07/21 | 09/07/21 |
| 2 | Peter | 08/07/21 | 09/07/21 | 09/07/21 |
| 3 | Leslie | 08/07/21 | 09/07/21 | 09/07/21 |
| 4 | Karla | 09/07/21 | 09/07/21 | |
| 5 | Susy | |||
| 6 | Ed | 09/07/21 | 09/07/21 | 09/07/21 |
My desired output will be another column called "Missing Assignments" with the name/number of the missing homework like this:
| ID | Name | Homework 1 | Homework 2 | Homework 3 | Missing assignments |
|---|---|---|---|---|---|
| 1 | John | 09/07/21 | 09/07/21 | 09/07/21 | |
| 2 | Peter | 08/07/21 | 09/07/21 | 09/07/21 | |
| 3 | Leslie | 08/07/21 | 09/07/21 | 09/07/21 | |
| 4 | Karla | 09/07/21 | 09/07/21 | Homework 1 | |
| 5 | Susy | Homework 1, Homework 2, Homework 3, | |||
| 6 | Ed | 09/07/21 | 09/07/21 | 09/07/21 |
The formula that I was thinking to use was
=IF([@[Homework 1]]<>"","","Homework 1")
And then apply the same logic for all the other assignments.. But I am sure that should be a better way of doing it. Because for this example it "works" since I have only 3 columns with the assignments, but how could I achieve it if I had 50 assignments instead of just 3? It seems kind of odd having to do it using the formula that I am using right now.
Another option could be using TEXTJOIN
=TEXTJOIN(",",TRUE,IF([@[Homework 1]]<>"","","Homework 1"), IF([@[Homework 2]]<>"","","Homework 2"), IF([@[Homework 3]]<>"","","Homework 3"),
However, I am still going to need to work with multiple IF´s.
What do you suggest me?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
