How to combine pricelists in Excel using xlCompare.

This is common task – you have 2 pricelists and you need to combine them into single list to add new records and analyze how the numbers was changed.
How xlCompare can help you?
xlCompare has set of commands on the ribbon, which performs various merging operations:
  • Combine worksheets
  • Intersect worksheets
  • Worksheets Difference
merge workbook commands on ribbon
All commands take pair of worksheets as input. Your pricelists should be on the different worksheets.
First command Combine worksheets, compares both sheets and produces new worksheet which contains all records from the source sheets. If your sheets has records with the same key (in your case this may be same items in the pricelists) xlCompare, in the output worksheet it will be represented with a single record. This allows you to see which fields were changed, in what is the difference.
For example:
Pricelist 1:
Code Price Description
Item 1 100 Description 1
Item 2 200 Description 2
Pricelist 2:
Code Price Description
Item 2 150 Description 2
Item 3 300 Description 3
As an output of the Combine Worksheets command you’ll get the following new list:
Code Price Price 1 Description
Item 1 100 Description 1
Item 2 200 150 Description 2
Item 3 300 Description 3
As you can see this list contains all records and all changed values. You can calculate difference of the changed values and analyze how the price was changed.
How xlCompare processes formulas when Combine Worksheets command is applied?
These 3 commands were designed to merge values, not formulas. New worksheet created with xlCompare will contain only values.
Is this feature only for pricelists, or I can use it for another tasks?
We don’t focus only on pricelist operations. This is common database-style operations, like INNER, OUTER JOIN and INTERSECTION. You can use these command to work with accounting statements, customer lists, ... They can be used anywhere with 2 worksheets which has database style (list of records).
Download xlCompare