Comparing data accross two worksheets..

[sh3896]

Hi

I want to create something to compare two worksheets that contain the same data requirement - albeit containing varying data due to new information regularly being added.

Sometimes I need to run comparisons between the data at two different dates and have been using a VLookup to do this. However, as far as I'm aware, a VLookup can only provide a general compare and I want something that will look at all the information, row by row and then say whether there's a general match (i.e., some key fields match but not all) or an exact match (i.e, all the information within a given row matches to a corresponding row in the other worksheet.

To explain what I want, I've attached a file that I've created with random data contained within (hence the A-O column headings). The first worksheet is where the "new" data would go and the second would be the data that i'm comparing against. I'm working on the premise that columns P & Q are already in the worksheet ready to go and that all I would do is copy and paste the data into columns A-O. That said, anything that anyone can suggest to make the tool more intuitive or even "cleaner" would be much appreciated.

My first time on here folks so go easy on me. Been very impressed with this forum so far, from my daily lunchtime peeks...

Thanks in advance for your efforts and assistance

[rylo]

Steve

Using your example file try putting the formula

=SUMPRODUCT(--('Old data'!$A$2:$A$5='New data'!A2))+SUMPRODUCT(--('Old data'!$B$2:$B$5='New data'!B2))+SUMPRODUCT(--('Old data'!$C$2:$C$5='New data'!C2))+SUMPRODUCT(--('Old data'!$D$2:$D$5='New data'!D2))+SUMPRODUCT(--('Old data'!$E$2:$E$5='New data'!E2))+SUMPRODUCT(--('Old data'!$F$2:$F$5='New data'!F2))+SUMPRODUCT(--('Old data'!$G$2:$G$5='New data'!G2))+SUMPRODUCT(--('Old data'!$H$2:$H$5='New data'!H2))+SUMPRODUCT(--('Old data'!$I$2:$I$5='New data'!I2))+SUMPRODUCT(--('Old data'!$J$2:$J$5='New data'!J2))+SUMPRODUCT(--('Old data'!$K$2:$K$5='New data'!K2))+SUMPRODUCT(--('Old data'!$L$2:$L$5='New data'!L2))+SUMPRODUCT(--('Old data'!$M$2:$M$5='New data'!M2))+SUMPRODUCT(--('Old data'!$N$2:$N$5='New data'!N2))

into 'New Data'!S2.

Copy this down to S5.

If you get 16, then you would be able to put an IF statement into column Q and bring back a "yes".

Up to you what number you pick to work out a general match. If you pick say 7, then again use an IF statement to bring back a "yes" into column P.

Alternatively you could have a scaling of poor, reasonable, good, great, exact..... and assign number ranges to the scaling. Put these into a matrix and have VLOOKUP bring back your scaled interpretation.

HTH

rylo

[sh3896]

Superb, thanks very much for your assistance on this rylo.

I copied the code over and took on board your comments....this is eactly what i was after.

Thanks again.