vba code or formula assistance to associate a date, text, and value to a summary report.

Hello, I tried searching the web for anything remotely close to doing this.. so I'm skeptical this can be done. With that said though, I thought before giving up I'd ask for any assistance or insight one could give from the forum.

I'm looking for a formula or vba coding that would look at the filtered name in column "A" then from row 1 between Columns C through Q in this sample workbook identify the date range and then starting with row 5 look for any of the following codes "8S", "7S", "6S", "5S", "4S" , "3S",, "2S", "1S" and associate them as one group . Ideally, I would like to assign a value to these codes ; i..e. "8S" = 8, "7S"= 7, etc and then via the formula have a result something to the effect below: (also I'd like this to populate on another sheet tab titled Report.

End result looking something like this:

John Doe,
Wednesday, 01/02/2013 4S, 4 hrs
Thursday, 01/10/2013, 8S, 8 hrs.
Total: 12 hrs.

If this is possible, your assistance would be greatly appreciated!