If a macro is acceptable, that seems easy. I won't supply detailed and tested code (sorry for that), but I can give some elements to combine. I've got the impression you would be capable to finish it.
Did you ever consider to use MS Access for the combined lists (if you want to stick to MS Office)?
On regular basis I have a need to combine sets of data in Excel. I have several teams working on projects and they submit lists of items in Excel. All of the Excel files have the same column headings, but the data underneath is different. I would like to combine this data into one master list. I looked at the concolidate, but this wants to do a calculation. I just need all my lists combined.
Sheet 1: Items to be completed Phase 1
Sheet 2: Items to be completed Phase 2
Sheet 3: Items to be completed Phase 3
Cheet 4: Combined list of items to be completed in all phases