How do you conditionally join text in Google Sheets?

Question

So, I thought this would be relatively straightforward for an advanced user, but this one is troubling my brain.

I have a schedule of people:

         1  2  3  4  5  6  7  8
John D.  a     a        a  l   
Jane D.  a  l  l           a  

I've indicated for which weeks they will be absent ("a") or late ("l"). Now, for each column, I want to summarize the people who are late or absent. Obviously, I have way more than two people, and this information changes gets updated regularly, so I'd like to do this automatically.

So, for example, I'd like column #1 to output: "Absent: John D., Jane. D". And for column #2 it would say "Late: Jane D." and column #3 - "Absent: John D.; Late: Jane. D".

Programmatically and logically, it's straightforward, but I can't figure out a spreadsheet formula to make this happen. Any help appreciated.

Answer 1

Since you consider yourself and advanced user I'm not going into many details and I'll assume that you know how to copy cells and what's the use of $.

Lets say that we have your example as is in the spreadsheet starting from cell A1. So the first absence of John D. is marked in cell B2.

Following the steps you'll end up in another sheet in your spreadsheet that will have the row with your desired columns.

• Rename the sheet with the data to input
• Add 3 new sheets and name them: report, absent, late
• Under the sheet absent in cell B3 paste that and copy it accordingly to the right and bottom:

=IF(input!B2 = "a"; CONCATENATE(input!$A2; ", "); "")

• Under the sheet absent in cell B2 paste that and copy it accordingly to the right:

=CONCATENATE(B3:B100)

• Do exactly the same steps for the late sheet by replacing the absence to late and "a" to "l"

• Finally, under the report sheet in cell B2 paste that and copy it to the right:

=CONCATENATE(IF(absent!B2<>""; CONCATENATE("Absent: "; absent!B2); ""); IF(late!B2<>""; CONCATENATE("Late: "; late!B2); ""))

The above example scales very well if in the future you would like to add more filters like sick, vacations, etc. You can check the final result in action where I also have another way of representing the report.