# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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

Replies
0
Views
447
Replies
0
Views
1K
Replies
2
Views
259
Replies
7
Views
248
Replies
10
Views
1K

1,172,201
Messages
5,879,634
Members
433,449
Latest member
Treavus

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