Compare rows in two sheets

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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.
 

Some videos you may like

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Feb 15, 2007
Messages
355
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

Active Member
Joined
Feb 15, 2007
Messages
355
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Feb 15, 2007
Messages
355
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top