Populate Table1 from Continuously Updating Table2, Table 3, etc

[tanman223]

I have multiple tables Table2, Table3, Table4 etc that get populated with new manufacturing data daily. These tables are in separate workbooks! From these tables, I want to populate Table 1 (a consolidation of all the pertinent data from other Tables, and it's own file/location) by running macros to refresh with the historical data and the newly entered data. The tables are constructed such that column A in all of them contains the Manufacturing Batch #s. In the other columns there are unique parameter names. If Table 1 is empty with just hundreds of columns of parameter names, and a person just updates Table1 with batch numbers in Column A, I would like to create a macro that populates Table1 with data from the other tables. I picture it as "where Parameter267 exists in Table2, copy data from Table2 to Table1 where Batch# matches". Then loop for all batches for that parameter, and do the same for all other parameters.

The order of the batch numbers between tables may be different or some may be missing, therefore I can't just copy entire columns.

 

[jdellasala]

Use Power Query to pull in tables 2,3, and 4, then merge or append them to make the final table (Table1){HUH?}. Once in place, Refresh All will update Table1.

I recommend watching some great playlists - Power Query, or
Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel which includes before and after sample files.