How to Compare and Merge Multiple Workbooks to Create a Master File

From: Naveen
Message: Hi,
I have 5 people working on a shared excel workbook and each one updates data (basically its a tracker) on his worksheet. I want that all their data gets transferred to the master sheet every day before they break off duty.(maybe with button for update data on their individual worksheets.

What i am aiming for is that there is no scope for mischief for e.g. one team member deletes data from another colleagues worksheet (inadvertently). I want to password protect each sheet individually and give each individual her/his password only and i have all the passwords including for master sheet (which nobody else will have).

Before we start creating complex solutions using Excel VBA let’s see how we can solve Naveen’s problem without VBA.
Microsoft Excel has an interesting feature called compare and merge workbooks hidden in the options area.

  • Click on file
  • Select options
  • In the Excel Options window select Customize Ribbon
  • Under ‘chose command from’ option select ‘all commands’ from the drop-down menu
  • Select Compare and Merge Workbooks
  • On the right hand side below the main tabs click on New Group to create a New Group under the Home tab
  • Now click on ‘Add>>’ to bring the option ‘Compare and Merge Workbooks’ under the new group in the Home tab
  • Click OK
  • You will now see the Compare and Merge Workbooks Option in your home tab under a New Group

Once the compare and merge workbooks option has been added we do the following:

  • Create a file and save it as a master file
  • We convert it into a shared workbook by clicking on Review and under the Changes Group we select ‘share workbook’ and then check ‘Allow changes by more than one user at the same time. This also allows workbook merging’.
  • In the ‘advanced tab’ in the Share Workbook window under ‘Track change’ we can change ‘Keep change history for… days’ option.
  • Click OK
  • Now we make as many copies of the master file as required and save them with appropriate names.
  • Next the team members receive their copy where they can make their entries or changes.
  • The workbook of the team member is then closed.
  • We open our master workbook or file and click on ‘compare and merge workbooks,,,’
  • In the new window ‘Select Files to Merge into Current workbook’ we select the relevant file and click OK
  • The data from that file is merged into the master file
  • We do this with files from all the team members to get all the data into the master file

View the Excel training video below for details.

One thought on “How to Compare and Merge Multiple Workbooks to Create a Master File

  1. zeak

    How to open the another Workbooks.Open(“c:\Documents\B.xlsx”) and create or save the Workbooks.Add(“B.xlsx”) and Workbooks.Save(“C:\Documents\B.xlsx”), if the Workbooks(“B.xlsx:) are not there…
    Thank you for your kind..


Leave a Reply

Your email address will not be published. Required fields are marked *