Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from superuser.com Mar 1 '12 at 5:54

This question came from our site for computer enthusiasts and power users.

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:
  • 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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.