Formatting imported data into a table with variable record sets

[ash6540]

Hi guys,
Thank you for reading my problem
I will try to keep the description brief - I receive an excel sheet with the results of training validation, the workbook is created automatically from a specialist scanning software to collated student responses to paper survey questionnaires. See attached example to paint the picture data sheet.

The number of questions varies from week to week and course to course as do the number of students.
The questionaire is broken in to three response area T, M, P for each question, each area has different responses.



What I am trying to do is collate the text response to each question and display them in a table. Sample attached Output sheet.

I have been able to count the number of responses to each question using formula, but have been unable to do this without changing the formulas for each time the number of questions changes.

Not sure how to tackle this - with formula, VBA or macros?
I am sure this is a simple problem for someone

Thanks