Got to be honest - the layout of the source files is going to make this quite difficult.

I'm going to step aside at this stage as I don't think my PQ skills are quite up to it, but I should add that it's always best to collate data in a completely normalised (flatfile) fashion, and then use formulae and pivot tables to present it in an aesthetic way. It's not good practice to put the cart before the horse.

But you have what you have and it's too complicated for me (layout and three separate tabs in the workbooks). Good luck with it.