Compare and show changes between two workbooks

[jonesl129]

Hi,

I'm looking for some help when it comes to comparing two workbooks. At first the workbooks were quite stable and didn't have no new entries or deletion of old entries so I initially used vlookup to give me what I needed and later found some basic VBA on the internet which did what I required quicker.

The workbooks have now changed slightly where each month new deliverables are added and old ones are deleted. This results in the entries of the two workbooks not aligning in the same rows/cells therefore when I run the vba it highlights 90% of the workbook as its just comparing cells against each other.

Each entry within the workbook has a unique identification number so I can still use VLookup for these however this doesn't allow me to see the new deliverables which have been added of the old deliverables deleted.

I hope i've made sense and if not please ask and i'll try to clarify any points.

Cheers,

 

[RG19]

What version of Excel are you using? Probably best to post a quick example.

 

[jonesl129]

What version of Excel are you using? Probably best to post a quick example.

Hi, I'm using Excel 2003 but we're upgrading soon to 2007. Below is an example of one of the worksheets. I've taken all the actual data and replaced it with example data but the columns are the same. Each month the workbook consists of approx 600 entries and each month there are new entries and old entries are removed hence why I want to be able to compare the data to highlight any changes to the Required by date and scheduled date and also list new entries and deleted entries between the sheets.

 

[jonesl129]

Just in case anyone looks for something like this in the future i've sorted it by doing the following.

I've created a new workbook where I've copied the data I require into two new tabs named "Current Month" and "Previous Month". I've then added 3 new columns at the end on each tab and used the following formulas to pull the "Required by Date", "Current Scheduled Date" and "Delivered Date" through from the other tab. If the data is not present it will either display "New Deliverable" or "Del Deleted". Along with some conditional formatting the sheet now gives me the information I require and will save me a huge amount of time manually comparing the sheets each month as all I need to do is copy and paste the data into the relevant tabs, overwriting the existing values and the formula's should hopefully do the rest for me.

On the "Current Month" tab to compare the current month of data with last months in cell H3 Ive used:

=IF(ISERROR(VLOOKUP($A3,'Previous Month'!$A:$I,4,FALSE)),"New Deliverable",VLOOKUP($A3,'Previous Month'!$A:$I,4,FALSE))

And on the "Previous Month" tab to compare last months data with the current months data i've used:

=IF(ISERROR(VLOOKUP($A3,'Current Month'!$A:$I,4,FALSE)),"Del Deleted",VLOOKUP($A3,'Current Month'!$A:$I,4,FALSE))

The sheet within the workbook I created now looks like this

Hope this makes sense.