Consolidating- Merging multiple worksheets into 1

Hi ,
I have multiple worksheets that I would like to merge into one summary sheet.
The worksheets all have data and some of the rows differ from sheet to sheet.
I tried using the Consolidate button at the top of the ribbon data>consolidate but when overlooking the data it is definitely not accurate. Having the same rows in each sheet is not convenient as there is too much data and it would create a lot of blank rows in the worksheet.

any suggestions would be great...

Thanks

Upload sample workbook that accurately represent your data set, with sanitized data to get better help.

To upload "Go Advanced" and manage attachments.

File added.
This is just a sample.
I actually have 8 sheets to merge into one.

Is there also i way to have it update when one of the other sheets are updated with actuals?

Thanks