Comparing 2 worksheets and displaying differences

skopf85

New Member
Joined
Aug 29, 2019
Messages
5
Hello,

I need to find a formula that will compare data across 1 column in 1 worksheet with 1 column in another worksheet and report back on whether the data appears in both columns or not. Example scenario: A customer sends across a list of product codes they would like us to quote, i have this list in sheet 1 on excel. The quote is then put together in sheet 2 however the products may not appear in the same order. I want to be able to check that we have covered off all the products customer requested and also identify any which do not match.

I found this formula which should do the job:

=IF(Sheet1!A1<>Sheet2!A1,"Sheet1:"&Sheet1!A1" vs Sheet2:"&Sheet2!A1,"OK")

This would check the same cell in each sheet and tell me what info is displayed in each cell however i want it to search within the whole column, so in theory the blow should work as it's a range.

=IF(Sheet1!A:A<>Sheet2!A:A,"Sheet1:"&Sheet1!A:A" vs Sheet2:"&Sheet2!A:A,"OK")

It still seems to only check the same cell though. Any ideas on how i get it to check the entire column?

Many Thanks

Sharon
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi & welcome to MrExcel.
How about in Sheet2
=IF(ISNUMBER(MATCH(A2,Sheet1!A2:A1000,0)),"Ok","")
 
Upvote 0
It looks as though this may be a way around it, would just need to run on both sheets to find all the changes.

Many Thanks

Sharon
 
Upvote 0
You're welcome & thanks for the feedback.
To use the formula on the other sheet as well, just change the sheet reference in the formula.
 
Upvote 0
As i want to see the results of both on 1 sheet i created a 3rd sheet which is looking up the data in sheet 1 and checking it appears in sheet 2 and vice versa. However Sheet 2 has less data than sheet 1, at the moment the formula will keep populating with blank space to indicate a problem, is there a way i can get it to identify it has reached the end of the data on that sheet? otherwise i'm going to be looking for problems that aren't there.
 
Upvote 0
Not sure I understand what you're saying, but would this work.
=IF(Sheet2!A2="","End",IF(ISNUMBER(MATCH(Sheet2!A2,Sheet1!A2:A1000,0)),"Ok",""))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top