Re-arrange data

It can also be done without using VBA, as in the attached.

This solution uses a new column (blue text) which concatenates info from your input data: you can hide this column. However, if you have more input data than in your sample, fill down in this column so that the formula extends at least as far down as the last input row.

The solution itself is in M3:V26, which in its current form will handle Tests 1-22 on Sheets 1-8. If you need to handle more Tests or more Sheets, just fill down or right from the existing range.

To make the filling down or right simple, the labels for up to 8 possible Sheets and up to 22 possible Tests have been entered in O3:V3 and M5:M26 respectively (green text). You should hide these by changing the green text to white. The labels in those ranges only appear to end users in Row 4 and Column N (black text) when there is corresponding data in your input range. In other words, if there is no Test 5 in the input data, the "Test 5" header will not appear in the results, and if there is no Sheet 7, that label won't show either.

Finally, there is conditional formatting throughout N3:V26 which adds borders only as required in the solution: this conditional formatting will also expand into the enlarged solution range if you fill down and/or right.