Looking for differences in similar data sets

Hi There.

I am analysing a sales order book on a week to week basis. The Sales order reference remains the same but order values can change daily based on shipped/cancelled and amended orders. Can anybody suggest the best way to reconcile these differences in excel please?

Hi Shirley, I responded to

Hi Shirley,

I responded to another thred that it is hard to give you the best method with out a clear example of what the data you are trying to compare looks like. But I can give it a shot while making a few assumptions:


1. You have two seperate sheets, one with the original data and one with the new updated data.

2. You have two columns of data that will be used in the compareson ('Sales Order Reference Original' and  'Sales Order Reference New'

3. There is only one record (or line item) of each Order Reference number. In other words, each row contains a unique reference number.


In my assumptions here is what the two sheets would look like:

Original sheet:

New Sheet:

To see the change in each row place this formula in cells C2 in the 'New' sheet and drag the formula down:


Your new sheet would look like this showing you the difference between your new and old sheets. Positive numbers indicate a positive change in quantity:

If you want to see only the records that have changed then filter out the items with no chage.

Let me know if this doesn't help or if you can provide an example of what you are looking at then I can help you with a more specific method.