mingali,
I think you mainly need to think "out of the box", I tried to re-shape your data, and presented them differently in order to obtain what you requested. Look to the end right of your spreadsheet, I've included my changes and ideas and hoping this will help you. There would be some minimal manual entries, BUT nothing can be entirely automated.
Let me know if you have any comments.
Bookmarks