Comparison on 2 sheets base on a unique name

[Ann_BBO]

Hi All,

I offer my apology if this question has been presented before, but here is my issue!

In the attached file, it has 2 sheets which are “File2” and “File1” for comparison. Since the “Name” in column E is unique, I would to compare the 2 sheets base on “Name” and then output the difference to a new workbook as a report.[Example, in “File1” sheet, name “.sys_cp_param.setup_access.sw_emc” is in offset 0x0f (i.e. Row 26), However, in “File2” sheet, this will shift in offset 0x19 (i.e. Row35)]. Actually, I have written the marco in this file called “CompareSheets”. It will show any differences in yellow color. However, this marco is based on the row to row comparison (i.e.Fixed comparison). If any offset is changed, the result will be wrong after the changed offset.

I know my English is not good so that you may not understand my meanings. Please try to see the attachments and report will be attached in next post.

Thanks

[Ann_BBO]

The report format is attached in this post.

[Bob Phillips]

You have the classical matching dilemma here. namely no key to match on.

Without a key field, how can you determine whether a row in one sheet is/is not within the other sheet? And how do you know when it is back on track?

Also, the use of merged cells makes it more difficult to match.

[Ann_BBO]

Hi xld,

Thanks for your reply. Refer to attached files, My Strategy is using For...Loop (say the range from 1 to 1000) to get the name in file1 first, then search this name in (pool) File2. If the name in File1 is match with name in File2, then compare their offset and their value. If it is identical, move to next name in file1 and then repeat the steps. Otherwise, list out their difference report such as File1 have or File2 have or It is offset change issue....

BTW, would you mind to give an example of classical matching dilemma for my study. thanks.

Thanks,
Ann