# Compare rows in two sheets

#### sjha

##### Active Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### DonkeyOte

##### MrExcel MVP
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

##### Active Member
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

##### Active Member

=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

##### MrExcel MVP
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

##### Active Member
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!!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,961
Messages
5,856,531
Members
431,819
Latest member
Tori Murphy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back