Comparing two sheets with different layouts for differences

Hi, I'm fairly new to using VBA and I'm stuck with creating a script that will check through two sheets for any differences and output the summary onto a third sheet.
When preparing the data I need to move around columns and rows so the order will mostly be different to how it was originally. it needs to be able to check for inconsistencies in the values and also if there are any rows or columns missing/added.

Here's an example of the end result I'm hoping for:

Original data sheet
Example1.jpg

Prepared data sheet
Example2.jpg

Summary sheet
Example3.jpg

So as you can see the value for 'Item 4' in store has changed somewhere along the workflow and the check will alert me of that.
The sheets themselves can have hundreds of stores an items so it needs to allow for that as well.

If anyone can help me with creating the script / pointing me in the right direction of how it can be done I'd really appreciate it.

Originally Posted by Richard Buttrey

In order that we can understand the scale, how many stores and how many items are there. i.e. what are the dimensions of the tables.

Would you also upload a representative workbook. It needn't consist of full size tables, but ensure it contains both missing items and difference in quantities.

Hi Richard, thanks for the response.

The number of stores/items can vary from just a few to hundreds as they're orders from different clients.

In the attached workbooks 'New' tab, the values that have changed are highlighted in yellow, Store 9 missing and item 7 missing

Sorry to bump this but is anyone able to help me out please?

Here is an example project I located for you. Hope it performs as you need it to. Results are written on Sheet3

Please Login or Register  to view this content.