Combining 3+ lists into master list

I've spent hours and hours trying to figure this out with different concepts lol any help would be much appreciated. I have attached a dummy file here. The real sheet I am working on is much bigger with private information so this dummy created is relatively similar to express my dilemma..

I have excel 365 & preference of use on formula; vba/macro.

How can I generate a master list on all the week sheets containing all information including a disguised column on the notes section? I like for the master list to identify and combine the current, new and terminated names aswell instead of duplicating.

I prefer to combine several sheets into one on a weekly basis from other workbooks to collect data (however in this case I have just copied and paste the sheets from separate weekly workbooks - all tables remain the same, the only changes are the names and data). Any better efficient way /input on this would be great.dummy.xlsx

That was a fast and awesome fix. Thank you Glenn,

Possible I can sum/combine the days(numbers) and notes(text) column from all 3 sheets aswell?

-my last mention was, would there be a better way to add data to the master list from multiple workbook files? What I have been doing is copying the tabs/sheets and transferring them to the master list workbook file.

Power Query

Please Login or Register  to view this content.