VBA help finding latest date and moving ranges

Hello from Wisconsin,
I'm a new member (first post).

The boss wants me to reformat a spread sheet (attached).

Desired format is:
- To have each student's information contained in one row.
- Survey results only displayed for most recent date.
- Number of times attended each course and total courses.

Haven't used VBA in years and trying to re-learn it. (Excell 2007)
I'm having some success using offsets and for next loops to handle data for one student, but haven't been able to figure out a procedure to make it work for all students.

I've attached a sample data set with the desired format beneath it.

training_excel.xlsm

Any help would be appreciated.
Thanks
Dave

maybe so (see attachment)

nilem,

Excellent...
Thank you