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.