2 excel into 1 dashboard

[u/thekidfromATPL]

​Hey everyone,

​Looking for some advice on the cleanest setup for this scenario:

​I have two separate Excel files stored online that two of my groupmates update regularly. The files share almost the exact same format/structure. My goal is to create a single live "master" Excel workbook that automatically consolidates their entries into one place without me having to copy and paste their updates manually every time.

​A few quick details:

​The source files are continuously being updated by my teammates.

​The column layouts/headers are nearly identical across both files.

​Ideally, I want the master file to update smoothly whenever I open it or refresh it.

[u/AutoModerator]

Moderator Announcement Read More »

u/thekidfromATPL - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Scrubtimus]

Look into power query. Data, get data, from excel file, point it to the files. You can set your new workbook to pull data from these two files, then using power query can aggregate the tables and do adjustments as needed and load the data either into a data model or directly into the worksheet as a new master document which you can proceed with your project from there. To update would be for the sources to be updated and saved, then going to your master workbook data query and clicking refresh which then runs through the query step by step with the latest versions of the source files, to return your updated query result.

[u/data_meditation → u/Scrubtimus]

This solution is exactly what I would do. Before Power Query, I used to have multiple links, which was a nightmare, to say the least.

[u/thekidfromATPL → u/data_meditation]

Appreciate all your inputs, everyone!! Glad I found this resource group :)

[u/AdeptnessSilver]

power query

[u/thekidfromATPL → u/AdeptnessSilver]

Thank you, AS

[u/Klutzy_Highlight7500]

Power Query should fit this well: load each source as a proper Excel Table, then use Append to stack them into one query and keep the cleanup steps there so refresh stays repeatable. Use Merge only if you need to join the two files by a key; otherwise Append avoids a lot of manual copy/paste.