Thread: Compare data in separate workbook or worksheet

Hi
I am new to this forum and in excel as well. Currently stuck to this problem, here how it goes : Im working with a huge amount of data in BOOK1.xls and once or twice a week I received an update of the list.

1. I need to compare and find out from the new updated worksheet if there is a new revision, and if there are new documents added to the list. I have tried Vlookup..but it didnt work for me.

Ex. Doc1 has revision 1, 2 in my BOOK1 that I am working on. Note that there is a repetition of the DocName, Doc Number except for the revision.

DocName Doc Number Revision
Spec Doc1 1
Spec Doc1 2
Spec Doc1 3??? <<--- New revision from BOOK2.XLS



Thanks very much and appreciate your help.

Hi,
I'm assuming that there could be the same revision numbers for different document numbers. i.e. the revision numbers in col C may not be unique. Since you don't have Excel 2007 then you'll need to create a helper column D in both documents. This should concatenate the column B&C values. i.e.

Code:

=B2&C2

and copied down.

Now in your new revision book, and assuming the older book is called 'Book1' use in E2 and copied down:

Code:

=IF(ISERROR(MATCH(D2,'[Book1.xls]Sheet1'!$D:$D,FALSE)),"New","")

HTH

Hi,

In Excel 2007

Code:

=IF(COUNTIFS('[Book1.xls]Sheet1'!$B:$B,$B$4,'[Book1.xls]Sheet1'!$C:$C,C2)=0,"New","")

Rgds