Matching values across sheets

[michael.sejrup]

Hi all

Looking for support to create a function to help identify related entries across sheets.

Criterias

• Column A in sheet 1 should contain more than one entry of the Top Item ID. E.g. 6083966 has 8 entries. If only one entry the function should return a comment e.g. "6083967 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)
• Final step is to check the text in column C. A pair of siblings should always have both texts "Only Supp" and "Only Eng". (Highlighted in Gray)

If all criterias are met the function should return an "OK Sibling XXXXXXX found" (Highlighted in Green)

I am sure I can do this in VBA but for the sake of other users I would prefer if it was possible to do this in a formula instead.

Looking forward to your feedback.

[pjwhitfield]

I have this close but no cigar, its correctly highlighting the 1st two criteria and is half way to doing the last one but is returning #NA for those matching rather than the value.

Please Login or Register  to view this content.

I'll keep looking at it but maybe your eyes might spot it straight away.

[michael.sejrup]

Hi
Thank you for your quick reply. I looked at it and modified the formula "a bit" so that it returns the sibling

Please Login or Register  to view this content.

I think this works. Next challenge is then to make sure the siblings match by looking at the Top Item ID and the Text Column. Grey and Green.
I have attached an updated workbook with the new formula included.

[JeteMc]

If I understand correctly one of the siblings should correspond to the text "Only Supp" and the other to "Only Eng". If that is correct then the following formula will work as shown in column I of the attached copy of your file:

Please Login or Register  to view this content.

Let us know if you have any questions.

[michael.sejrup]

Looking at the enclosed file the only thing missing now would be to check that the siblings have the same parent from column A in sheet 1.
I have tried to illustrate this in the enclosed workbook.

• Siblings 6023864 & 6179298 is a match because they have the same parent, 6083966
• "Fake" siblings 6023864 & 6179298 is not a match because they have different parents. 6023864 has parent 6083968 (Blue) and 6083969 has parent 6083969 (Grey

)

[JeteMc]

Paste the following formula in G2, press enter and double click to copy down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[michael.sejrup]

Final piece of the puzzle as I missed out on this in my explanation.

A couple of siblings are allowed to have different parents. I have tried to illustrate this in the enclosed file.

• 6023864 is a sister of 6179298. They have the parent 6083966 = Match
• 6023864 is a sister of 6179298. But they also have the parent 6083967 (orange) = Match.

At the moment if there are more siblings with different parents, only the first show as a match. Subsequent matches show as No Match despite being ok (highlighted in red). Looks to me like it might have something to do with the index match part of the formula?

[JeteMc]

Some helper columns have been added (columns H:L), which may be moved or hidden for aesthetic purposes, and employ the array entered formula:

Please Login or Register  to view this content.

The (now) array entered formula* in column G reads:

Please Login or Register  to view this content.

The cells previously highlighted in red now display as match as well as G11 which I think is correct now that the 'Top Item ID' for that line is now being used more than once.
* Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.