Data On Multiple Sheets Presented In Table

[Deus_Ex_Machina]

I have no idea if I am dreaming with this but I have a series of spreadsheets (30) that output data that looks like the attached at the bottom of each sheet (one for each player).

https://1drv.ms/u/s!AiBk9WsyEESSgpVxt_0_XjHt6lYRbA

Now if I had to I could manually paste all these into one sheet (or record a macro i suppose) but then what I want to achieve is have the tests as the row labels and underneath a list of names that meet the criteria "FAIL"​

Like this:

https://1drv.ms/u/s!AiBk9WsyEESSgpVyRWhYf8Kgb0rufg

This is a process I'll have to do each time we do a bunch of tests every few weeks so I can do some manual processing but obviously want to make it as easy/easily repeatable as possible. Not really sure where to start though. Would greatly apreciate any help as this is the last piece of the puzzle of my masterpiece lol.

 

[Deus_Ex_Machina]

OK So I have gotten a bit further and this might better explain. With use of a macro I will be able to get all players across the top (30) and whether they passed a given test or failed. The ouput I'm trying to get is from row 19 down where because Cory and Colin failed they are listed under that test.