Compare rows in two sheets

[sjha]

Sheet1 has 'Week 1' data and Sheet2 has 'Week 2' data. Sheet2 will have 'Week 1' data as well as new Data. It is very possible that in Sheet2, 'Week 1' data has changed. There are no unique identifiers in either Sheets. Only if you combine cell values in Col. B, J, N, and O then it makes a unique row. So, based on this assumption what needs to be done so that it it identifies if there are new rows or changed rows in Sheet2? If so, I would like to see words like "new" or "changed" in Col. P in Sheet2. Thanks in advance for your time and help.

 

[DonkeyOte]

to go in row1 on sheet 2 in first available column (assumes range of 100 rows -- alter as required)

=IF(SUMPRODUCT(--(Sheet1!$B$1:$B$100=$B1),--(Sheet1!$J$1:$J$100=$J1),--(Sheet1!$N$1:$N$100=$J1),--(Sheet1!$O$1:$O$100=$O1))=0,"New/Changed","")

 

[sjha]

Thanks for your reply.
I pasted the formula you provided into cell P2 and copied all the way down to row 114 and for all cells I got 'New/Changed' as a result and that is not true. Is it possible to send a sample file?

 

[sjha]

Correction to your formula:

=IF(SUMPRODUCT(--(Sheet1!$B$1:$B$100=$B1),--(Sheet1!$J$1:$J$100=$J1),--(Sheet1!$N$1:$N$100=$J1),--(Sheet1!$O$1:$O$100=$O1))=0,"New/Changed","")

Changed to:

=IF(SUMPRODUCT(--(Sheet1!$B$2:$B$1000=$B2),--(Sheet1!$J$2:$J$1000=$J2),--(Sheet1!$N$2:$N$1000=$N2),--(Sheet1!$O$2:$O$1000=$O2))=0,"New/Changed","")

Now it is giving me #value error message.

 

[DonkeyOte]

did you change the references to meet your real data ?

The SUMPRODCT checks to find matches of Bx,Jx,Nx,Ox on the other sheet in the same columns in rows 1 to 100 -- if return is 0 then no matches were found.

 

[sjha]

Thank you very much. Actually, it was my bad. After altering the formula, it is working now. This is awesome stuff. I had heard the most powerful formula in excel is sumproduct and I must say 'Indeed!'. Thanks again!!