Copying record sets to eliminate blank lines

I have a workbook that uses a macro to read information out of approx 450 workbooks.

The data is then compared with another data source for analysis.

Each record contains 14 lines of data and on separating line.

What I would like to do is to have another sheet read the raw data, remove the empty lines and insert in column A the book name against all 14 lines.

A copy of the source and desired result is attached. I would prefer a formulaic solution rather than VBA

Grateful any thoughts.

Ed