Comparing 2 worksheets and displaying differences

[skopf85]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about in Sheet2
=IF(ISNUMBER(MATCH(A2,Sheet1!A2:A1000,0)),"Ok","")

 

[skopf85]

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

 

[jkpieterse]

Perhaps this little tool might help:
https://jkp-ads.com/download.asp#Compare2Tables

 

[Fluff]

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.

 

[skopf85]

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.

 

[skopf85]

Perhaps this little tool might help:
https://jkp-ads.com/download.asp#Compare2Tables

Sadly i cannot apply the add-on as i'm needing this on a work computer.

 

[Fluff]

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",""))

 

[skopf85]

Yes that's perfect thank you.

 

[Fluff]

You're welcome & thanks for the feedback