Identify changes between two sets of data

Evening all,

I attempting to identify changes between our stock position day on day.

The attached file shows a simplified version of our stock report (there are thousands of rows in reality and more columns of data).

What I'd like to achieve are the 4 sheets at the end of the attached spreadsheet - summary, additions, despatches and adjustments.

This data can be retrieved from analysing differences between the yesterday and today tabs - showing stock position for yesterday and today.

Additions are simply stock ID's with ID numbers greater than the greatest stock ID from yesterday (our stock is given a unique ID in ascending order).

Despatches are any ID's that were on yesterdays data but aren't on today.

Adjustments are ID's that are on both reports but where the quantity has changed (scrap losses, etc.)

I'd like a summary report to summarise the total movements and unique tabs identifying the movements in more detail (as in the attached file).

I hope I've explained myself clearly, all help is appreciated.

Thanks!

hi, Swanseaexcel, save attachment and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. run the code

Please note that sheets names are hardcoded in macro. In case you change sheet names you'll need to change sheet names in the code as well.