Fairly complex - matching "siblings" and "parents" across sheets

Hi all

Tried to solve this problem using formulas which was manageable, but only by adding several helper columns and having a formula too complex for "normal" users to read and understand.
So now I try to have it done with VBA instead.

In short the task is to find pairs of "siblings" with the same parent. See enclosed workbook for illustration.

Outcome
Range with comments in sheet 1, column D

Information
Sheet 1 is the main sheet. This is where the matching should be done
Sheet 2 contains all siblings. This sheet is used for identifying siblings

Criteria

• Column A in sheet 1 should contain more than one entry of the Top Item ID. E.g. 6083966 has 9 entries. If only one entry the function should return a comment e.g. "6083977 only one entry". (Highlighted in blue)
• If more than one entry in column A sheet 1, next step is to check if the Item ID in column B. If the Item ID has duplicates in either column A or B in sheet 2, the function should return a comment "Multiple entries in Sheet 2" (Highlighted in orange)
• If there is only one entry in either column A or B in sheet 2, the function should match the related sibling. E.g. Item ID 6023864 has a sibling in sheet 2, 6179298. This goes both ways so that for Item ID 6179298 the function should return sibling 6023864 (Highlighted in Yellow)
• A pair of siblings should always have the same parent. E.g siblings 6023864 & 6179298 has the parent 6083966 = OK. But there is also a pair of siblings with different parents = Not OK (highlighted in red)
• If a pair of siblings have the same parent last step is to check if the pair of siblings have both texts "Only Eng" & "Only Supp". If not = Not OK (highlighted in grey)
• Examples of successful matches are highlighted in green.

My biggest problem so far with this problem is speed and ensuring all criteria is met. Main sheet 1 can contain anywhere from 2000 rows up to 20000 rows to be checked. Sheet 2 contains anywhere from 2000 rows up to 6000 rows.
I am thinking populating the ranges in arrays and doing the comparing there would be the fastest approach.

Looking forward to hearing from you.

Ideally I just want to know the good combinations, the rest is irrelevant, at least on paper . I do find it useful though to add a comment as to why they are "bad". E.g "two entries in sheet 2" etc.
A good pair of siblings have the same parent in column A, and one of the siblings has the comment "Only Eng" while the other has the "Only Supp" comment

Yes in order for a pair of siblings to be "good" the pair needs to have both "Only Eng" + "Only Supp"
A child with no sibling = bad. No entries would be found in the table in sheet 2 so this would be an orphan
A child with more than one sibling = bad. If a child is present more than once in the table in sheet 2, this should be disregarded.
A child/parent pair will be unique. You can have one parent with multiple sets of siblings. But there should never be more than one pair of siblings with the same parent more than once.
A pair of siblings can have different parents also, which is ok. As long as the pair has both texts.

• Yes this is also ok, as long as the sibling 6179298 is found and that they both have the same parent.
• Parent 6083966 has a pair of kids 6023864 (Only Supp) & 6179298 (Only Eng). So this is a good relationship (marked in green)
• The second time 6023864 appears it is with parent 6083977. As this parent is only present once in column A in sheet 1, the relationship is incomplete (marked in blue)
• Third time 6023864 appears it has parent 6083968. Siblings of 6023864 can be found, but not with the same parent.
• Another good relationship would be parent 6083967 with siblings 6023864 & 6179298.
• The last relationship with parent 6083888 is not ok, as the text belonging to each sibling is the same. In this case "Only Supp"

I was thinking if it might be an idea to sort the data according to the Top Item ID and then for each parent check if good relationships exist? Once the search within one parent is done, repeat the loop with the second parent.