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.
Best Answer
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 ofJohn D.
is marked in cellB2
.Following the steps you'll end up in another sheet in your spreadsheet that will have the row with your desired columns.
input
report
,absent
,late
absent
in cellB3
paste that and copy it accordingly to the right and bottom:absent
in cellB2
paste that and copy it accordingly to the right:Do exactly the same steps for the
late
sheet by replacing theabsence
tolate
and"a"
to"l"
Finally, under the
report
sheet in cellB2
paste that and copy it to the right: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 thereport
.