'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