Find Multiple Occurrence of ID within ranked list, return unique ID

Hello,

I am trying to resolve a complex relationship between Child ID's belonging to Parent ID's, according to a ranked list.
An ID can exist in multiple forms as a child to different Parent ID's.

Please see attached example of 20 records in which I need to create a unique set of ID's in which a child ID is eliminated if it exists within another (parent) ID.

For example:
ID 1020 exists as a child of ID 1019. However, ID 1019 is a child of ID 1003. However, ID 1003 is a child of 1002.
Therefore, I now need to check where ID 1020 has a second relationship. It is a child of ID 1016. However, ID 1016 is a child of ID 1008. However, that is a child of ID 1006.
Therefore, I now need to check ID 1020 has a third relationship. It is a child of ID 1005 which has no other relationship.

So ID 1020 is not unique and is excluded. But ID 1005 is - which is retained.

Another example:
ID 1001 and ID 1002 have no other relationship so they are unique.
ID 1003 is a child of ID 1002. Therefore, ID 1003 is immediately excluded. Any children of ID 1003 can now be checked for any other relationship.

Another example:
ID 1019 is a child of ID 1003. However, ID 1003 is already excluded so ID 1019 is unique.


I have thousands of records in total where an ID is a child of many, higher ranked ID's. Can anyone please help with a process of investigating unique ID's please?
Thanks SO much!

Ben.Unique Rank Example.xlsx

Thanks for the quick response.

Correct, 1003 is eliminated as it is a child of 1002. As 1003 is excluded from the analysis, ID 1019 also remains as it is only a child of 1003.
In each instance, the highest ranked ID is kept, unless it is a child of a higher ranked ID. Any other ID is also eliminated, unless it's higher ranked Parent ID has already been eliminated.

I should also add. In my actual source data, the 8000+ records I have is contained in just three columns:
ID, Rank, Child ID

ID 1003 is eliminated because it is a 'child' of ID 1002 (which has a higher ranking). Therefore, ID 1003 in effect no longer exists as a Parent ID of 1019.
ID 1002 is a confirmed record; now ID 1019 can be a confirmed record as it is not a child of any other ID.

Thanks for taking a look at this. This isn't the solution, however.

For example, ID 1020. I need to be able to check if this ID belongs to a higher ranked parent ID. In this case, it has three parent ID's and ultimately the result should show that ID 1005 is the only Parent ID which doesn't also have a higher ranked Parent ID with a higher ranking.

A unique list of (parent) ID's, where that particular ID is not a child of a higher ranked ID. The updated attachment is probably a better example to use.

I have a list of 5,000+ records in total which are set out in the same fashion as this example.

If an ID exists as a child of a higher ranked ID then this is eliminated and removed from the equation.

Take ID 1010 as an example, ranked 10th. This already exists as a child of ID 1004 (ranked 4th). However, ID 1004 is a child of ID 1003 (ranked 3rd).
ID 1004 is therefore removed.
So now I need to check if ID 1010 is found elsewhere and it is, as a child of ID 1005 (ranked 5th). However, ID 1005 is a child of ID 1001 (ranked 1st).
ID 1005 is therefore removed.
So let's check the existence of ID 1010 once again. It is also a child of ID 1007 (ranked 7th). However, ID 1007 is a child of ID 1006 (ranked 6th).
However, ID 1006 is a child of ID 1003 (ranked 3rd).
ID 1006 is therefore removed.
ID 1007 is also removed as it is also a child of ID 1003.
ID 1010 is then checked once again for its appearance in the second column and it is found as a child of ID 1009 (ranked 9th).
ID 1009, however, is a child of ID 1008 (ranked 8th). ID 1008 is also a child of ID 1006 but this ID has already been removed from the checking process.
ID 1008 is also a child of ID 1002. However, ID 1002 will have been removed from the check as it is lower ranked than ID 1001 which it is a child of.

Therefore, ID 1010 is retained as a unique ID because all of its occurrences as a child of a higher ranked ID are instances where that higher ranked ID has been effectively removed from the process.

This exercise has to be repeated for each and every ID.Unique Rank Example v2.xlsx

There is no output yet. The example file is how the data currently sits. I need to provide the output as described in my previous post.

The result of the example would be this as per this: Unique Rank Example v3.xlsx

....ID 1007 isn't included because it is a 'child' of 1006 and 1003. The higher rank ID is ALWAYS retained, unless that ID is also a child of higher-ranked ID.
1006 is excluded because that ID itself is a child of 1003. 1003 is not a child of either 1002 or 1001 so 1003 is retained and therefore 1006 and 1007 are excluded.

1009 is excluded because it is a child of 1008 and 1005, 1004 and 1002. Only 1008 is retained from that list, however, as the three other ID's are children of higher ranked - and retained - ID's.

Thank you for this suggestion. I have copied the formulas into my file but unfortunately the final result comes up with a SPILL error.

Thank you Hans - this works. You are a magician!!
Thank you so much for your assistance and you too Ali.

(By the way, I didn't really understand your explanation, whereas I could follow my own - but hey, ho - we got there in the end!!!!