Compare two worksheets of separate data

[LJones60]

Hi,

I'm after some help to try and speed up my job of comparing two months worth of data. Each month I get a download from the system which details all the deliverables that are due to be delivered to the customer. The spread sheet looks like the one below.

The document that I recieve has around 900 lines of deliverables listed with new and old being added and deleted each month and part of what i have to do is to compare the current months data with the previous months data and highlight any discrepancies within (excluding the comments column as this is updated monthly)

At present I copy and paste the information side by side and then go right through the two lists aligning the deliverables by ID number as the data is not always in the same rows. As you can imagine this takes hours to complete. I then use If statements that compares last months required date with this months required date, forecast dates, actual dates, deliverable type, etc. to give me a simple 'Yes' or 'No' result on whether the data is the same. If its not then I can see what has changed. Doing it this way is really long winded and takes best part of a day when i'm sure there is an easier way to do it.

Can anyone help with some VBA that will compare two worksheets and automatically highlight to me the differences between last months and this months data, any new deliverables that have been added in month and any deliverables that have disappeared between months.

I hope the above makes some sense and any help is greatly appreciated.

 

[SteveO59L]

Have a look at vlookup()

 

[LJones60]

I was wondering whether VLookup would do this however its one of excel functions that I haven't mastered and haven't found the time to learn it properly. I'm quite good with formulas, recording macro's and editing the VBA once recorded etc, however at this moment in time I think I would struggle with using VLookup.

 

[SteveO59L]

Vlookup() would provide you with the functionality you are after, no need for VB

 

[LJones60]

Can anyone help me with the VLookup or know of any good guides so I can try and learn it?

 

[SteveO59L]

if you send me a pm, with your e-mail address, I'll send you a sample sheet

 

[VoG]

Take a look here http://www.contextures.com/xlFunctions02.html

 

[LJones60]

Ive just read down through that guide but I still can't see how I would use VLookup to compare two worksheets to show me all the changes, new deliverables added and old deliverables deleted.

 

[VoG]

Maybe this http://download.cnet.com/Excel-Overlay/3000-2077_4-10963782.html

 

[LJones60]

unfortunately i'm on a networked computer and we can't install any 3rd party software onto our system. Reading the description of that software though it takes two sheets, overlays one on top of the other and shows the differences in each cell. I found some VBA code that does that however it doesn't work for me as the data can be in different rows month on month meaning the data is immediately different when comparing the two worksheets.