Complex automated task

I need to create a complex automated task.

I have attached a workbook sample with keys and tables to show what each code represents. Its pretty simple when you take a moment to look at it.

My goal:
Compare the Product Codes (first 4 digits) from Product codes to Product Codes from another file (Also first 4 digits). To see whether they match according to their row.
Also the Weight Codes (last 3 digits) from Product codes to Product codes from another file (also last 3 digits). To see whether they match according to their row.

But!...

There’s a slight complex in the Weight Codes.
I have a code here to explain it in code language instead of English language:

Please Login or Register  to view this content.

Because 396, 400 weight codes are linked to 101 (Because they are all 1kg product, doesn’t matter if its 6 x 1kg or 10 x 1kg. Simply means it is a 1kg product). They should be a true value compared to their row. But if its not specified inside the {} then it’s a false value. Hope this makes sense.

I’m not sure how to set out a sheet or maybe need another sheet creating to compare the data? Or can everything be coloured (Conditional formatting) on 1 sheet?
My creativity with excel sheet isn’t great. I would be grateful If someone could be imaginative and create it for me.

Look forward to the replies.

edit:Leaped before I looked