Seems like hard work.
As a possible alternative, have a look at the pivot table in the attached and see if it gets anywhere near what you want (pivot tables are very flexible).
If you try to refresh the pivot table it will surely complain that it can't find the file; what you need to do is adjust cell A1 (yellow) to point to the location and name on your hard disk of that file that you attached in your message, then refresh the pivot.

Once it's working with that same file, you can try using other source data files.

Attached Files

MSOfficeForums50983.xlsx (79.8 KB, 4 views)