Listing multiple output values within a single equation.

[tagordon90]

Hello all,

I'm new to the forum and hoping to make life a bit easier. The data is simple, wanting to know whether or not a student has completed their assignments each day. I've input a COUNTBLANK formula to output "Incomp." when the homework has not been completed (pictured).

However, I'm hoping to take this one step further. In the next sheet, I would like to have a single column that states all of the incomplete homework days next to each student's name. I attempted this with the formula: =IFS('Math-CB'!E79="Incomp.","Day1", 'Math-CB'!F79="Incomp.","Day2", 'Math-CB'!G79="Incomp.","Day3", ....) so on and so forth throughout all 30 days. For instance, if John Doe did not complete homework on days 3, 4, and 11, then this formula would produce the final result: "Day3, Day4, Day11".

The IFS formula I attempted above would not go beyond first truth, therefore would only produce a single output value upon the first "Incomp." it found. For instance, although John Doe had incompletes on days 1, 2, and 3, the output value only displays Day1 and foregoes listing Day2 and Day3.

Is there a formula or macro that would go beyond the first truth found, and list every item in which the truth occurs within a single cell?

Any help or tips to make this happen would be greatly appreciated!

 

[Mayanwolfe]

The Excel IFS function runs multiple tests and returns a value corresponding to the first TRUE result it finds. So it's basically doing the exact opposite of what you want.

This can be done with formulas, through the use of "helper cells" on your Summary sheet. On the summary sheet, start in cell D2 and use the formula
=IF('Math-CB'!E79 = "Incomp.", 'Math-CB'!E$1, ""). Drag this formula to the right so that it is checking the values of all 30 days, then copy the whole thing down until you reach the end of your student list. This will only populate the helper cells with a Day value where it finds the word "Incomp." The lock ($) will keep the row reference anchored on the top row, so it will return the day value for all students. This completes your helper cells.

Now, starting in C2 on your summary sheet, you can concatenate all the helper cells together. Correct me if I'm wrong, but it looks like you're using Excel 365, in which case you have access to the TEXTJOIN function. In C2, type =TEXTJOIN(" ",TRUE,[your helper range here]). Replace the red text with your full horizontal range of helper cells. This will give you a list of all days the previous formula returned, delimited with spaces and ignoring any blanks. Copy this formula down so it covers your full student list. After you confirm that it works, you can hide your helper cells so it looks cleaner.

I'm sure this can also be done with a macro, but I'll leave that for more skilled practitioners to provide. Hope this helps.

 

[Fluff]

No need for helper cells, you can use this
=TEXTJOIN(", ",1,IF('Math-CB'!E79:AH79="Incomp.",'Math-CB'!E1:AH1,""))

 

[tagordon90]

This worked! Thanks for the help!

 

[Fluff]

Glad we could help & thanks for the feedback