I want to know if its possible to compare two excel sheets and update the one with new data from other.

sheet1 (source list) contains list of suppliers with invoices (and prices). sheet2 contain the unique list of suppliers extracted from sheet1 and the sums of invoices. sheet1 is update every month and it changes the amount of suppliers. I want to know how to update sheet2 with new suppliers from sheet1 automatically and if its possible without using macros.

  • A pivot table could do this. – Raystafarian Feb 23 '15 at 10:20

There is a built-in "merge" capability in Excel 2007. Here is the office page that describes it : https://support.office.com/en-ca/article/Merge-copies-of-a-shared-workbook-62e40030-12d9-450f-8efd-e2f62135eaa7?CorrelationId=6ffca5c5-539f-457b-8e12-509e3cc5f057&ui=en-US&rs=en-CA&ad=CA

These are the instructions copied from the Office webpage :

When a shared workbook is updated by other users, and you want to compare the changes that they have made before you update the workbook with those changes, you can use the Compare and Merge Workbooks command. This command is not available with other shared workbook commands on the Review tab in the Changes group, but you can add it to the Quick Access Toolbar.

NOTE You can only merge a shared workbook with copies of that workbook that were made from the same shared workbook. You cannot use the Compare and Merge Workbooks command to merge workbooks that are not shared.

IMPORTANT To use the Compare and Merge Workbooks command, all shared workbook users must save a copy of the shared workbook that contains their changes and use a unique file name that differs from the original workbook. All copies of a shared workbook should be located in the same folder as the shared workbook.

If needed, do the following to add the Compare and Merge Workbooks command to the Quick Access Toolbar:

Click the Microsoft Office Button Office button image, and then click Excel Options.

In the Customize category, in the Choose commands from list, click All Commands.

In the list, click Compare and Merge Workbooks, click Add, and then click OK.

Open the copy of the shared workbook into which you want to merge the changes.

On the Quick Access Toolbar, click Compare and Merge Workbooks.

Quick Access Toolbar with Compare and Merge Workbooks button

If prompted, save the workbook.

In the Select Files to Merge into Current Workbook dialog box, click a copy of the workbook that contains the changes that you want to merge, and then click OK.

TIP To merge the workbook with multiple copies of the shared workbook at the same time, hold down CTRL or SHIFT and click their file names, and then click OK.

|improve this answer|||||
  • It would be better to have the pertinent information here and provide the link for reference. If the link goes dead then you have no answer at all. – Mokubai Feb 23 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.