Automatic data comparison between two sheets

Hello guys.

I'm in big trouble here. this excel problem drives me real headache.Hope you guys can help.


Let me explain. I have two worksheet.

1)"Checklist" worksheet = will compare and detect any similar values in its column (A and B) , with value in column (A and B) in worksheet "submitted"/( A&B(checklist) = A&B(submitted) ). If it found any similar value, it will marked "Yes" in (column H). Can up to thousands row.

2) Checklist worksheet = will be manually updated everyday. Can up to thousands rows.


One good guy from this forum has given me the formula and one macro to adjust the range ( formula in column H in attachment). It perfectly works the way i want. But recently when the data is reaching hundreds,i just found there are some similar data that fail to be recognized and marked as "yes".

You can refer to attachment, i have highlight those rows in green in both worksheets just to indicate they are similar but fail to be detected.


So my question is , which part that i miss? Anyone can help me fix this?

Thanks in advance.