Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
add comment

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

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

1 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 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.

share|improve this answer
add comment

Your Answer

 
discard

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.