Compare each row in sheet1 to each row in sheet2 -no pair write in sheet3 - HARD

Hi,

I have an very big Excel file that has exactly same columns but different many rows. I need help with an VBA-script to compare each entire row in sheet1 with sheet2 If it dont finds an pair I want it to write the row in sheet3. Its very important that the script starts in row 1 in sheet1 and then loop true whole Range in sheet2 to check If it fond an pair or not. If it cant find an match then I want it to write that row in sheet3.

So:

Take first row in Sheet1 save it in temp (or something) then loop in Sheet2 and search for the entire row it picked up earliyer - if it dont finds an match then write the entire row in Sheet3.

Please need all help I can get!!

Using Excel 2010 - VBA script pls

THANKS!!!

Hi,

Thank you for the answer!
Im sorry but this will not work becuase if I remove all the dublicates I can not track the rows - Thats why I want them in Sheet3 so I can recognize all the difference and correct them.
Its also like 8-9 of 10 cols can be the same but not the last/first/middle one and then I want it to write the entire row.

I think that this is an very hard prob

Let say: It takes the first row (col A-Z) save the row in temp - loop true EACH row in sheet2 and try to match it. If it dont get an match write it in Sheet3 else nothing.

Really need an VBA script for it - I do this maually so many times

Hmm... I can't get it work? I get Errorcode nr 5.
Will this script takes the first row (col A-Z) save the row in temp - loop true EACH row in sheet2 and try to match it. If it dont get an match write it in Sheet3 else nothing.

Originally Posted by Bernie Deitrick

I have assumed that your sheets are actually named Sheet1 and Sheet2:

Please Login or Register  to view this content.

I have now upload an test.
Sheet1 is old data, sheet2 new data, sheet3 is expected result after we have run the script.
You can se the rows in sheet2 that are markable bold is the differences.

Originally Posted by Bernie Deitrick

Post a sample workbook.

Again, thank you so much for your patienece!!!

Hi Bernie!!!

This REALLY WORKS! SO GOOD!!!!!

I just wonder a few things, I can't get it work if the cols are less then to column "Z" - Get Errorcode: 5. How can I optimize it on fastest way so the script can calculate cols by it selfs and not be hard coded?

I also found that if I only leave one cell with value in Sheet1 (in the whole row) b4 I make the comparison with Sheet2 it can not find that the row is different from the data in Sheet2.

Know how to fix this?

Again, Im really thankful for your help and you have extreme excel skills - I have search for an solution for this problems in days but could not find anything.

Best regards, M

Thank you so much! it works as you say. This have solve 1 of my 2 problems hehe!
Im sorry if Im such a pain right now but Im wondering how the script can be remade so It it compare Sheet1 with Sheet2 write differences in Sheet3 AND compare Sheet2 with Sheet1 and write the differences.

Can maybe solve this manually so I can paste Sheet2 in Sheet1 and conversely or Im thinking wrong right now?

I wonder if the script can be rebuild so it recognize this type of differences? I also wonder if the cell that has the difference can be higlighted?



Example.xlsm

Hello Bernie,

Thanks 4 ur answer! In some other documents I will have unique numbers in col "A". Is it possible then?
Do you know why the script don't recognize differences like the one I post in the new excel document?

Thanks!

Originally Posted by Bernie Deitrick

To highlight the differences, you need to have something that will
1) never change
2) be unique

Like an ID number - if that exists, then you can use that to determine which two rows are meant to match.

Hmm... That one doesn't work so well. Its highlighted cells in the row thats miss an difference.
Can the first script not be rebuild so when it fins the difference it highlight it direct when it place it in sheet3?

Thank you!

Originally Posted by Bernie Deitrick

Try running this code after Sheet3 is created - the file you posted had a lot of repeated data; with unique values in A the code works fine (I think)

Please Login or Register  to view this content.

Hi Bernie,

Again, thank you so much! I really appreciate it. I attach an example workbook in this message.

Thank you!TEST2.xlsm