Creating an "Ultimate Parent" Column for a Parent-Child Relationship

[dcu5001]

Hi everyone. I've found threads that cover similar questions to the one I have, but none which touch upon exactly what I'm looking for. This is also the first time I've posted in here, so I apologize if I do anything incorrectly.

What I'm trying to do is create an additional column within Excel that shows the "Ultimate Parent" for each value in my data. Let's say I have a table that looks like this:

I want to create a new trigger column which shows the ID of each entity's "Ultimate Parent" (with Bank Headquarters 1, 2, and 3 being the "Ultimate Parents" in this example):

From what I've gathered looking around for any sort of help to this problem, the way this issue is resolved is through VBA since Excel doesn't have any sort of tree functionality. I am just okay when it comes to Excel, and have very little experience with VBA (even though I do have a coding background with SQL). I was wondering if someone could point me in the right direction to what needs to be done in order to get this new "Ultimate Parent" column...I've searched around a bunch of different sites and forums and haven't been able to find anything that works. Thanks in advance, and if there's anything I can clear up about my example please let me know.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You can do that with a formula. Assuming that data shown in your screenshot is located in Columns A:D, with the headers on Row 1, enter this formula in Cell D2 then copy down.

=IFERROR(IF(VLOOKUP(C2,$A$2:$D$1000,4,0)="-",VLOOKUP(C2,$A$2:$D$1000,1,0),VLOOKUP(C2,$A$2:$D$1000,4,0)),"-")

This assumes you're using "-" for no parent. If you are using 0's with a format that displays 0's as dashes, just modify the formula in two places replacing "-" with 0.

 

[dcu5001]

Thanks a lot Jerry, this is working perfectly for the example I used. I hate to bother you again, but I was wondering if you could clarify the terms of the formula a bit more. I oversimplified the sample data I was using just for the sake of creating a clear example of what I was trying to do, and when I tinkered with the formula a bit to fit my actual report it's resulting in some inconsistencies (ex. Ultimate Parent column showing IDs for those which don't wrap up into the specified Ultimate Parent ID. This might be a bit better example of what I'm trying to do with the data I'm working with:

Let's say I'm starting with a similar set of data, but this time around I only want two out of the three Bank Headquarters flagged, so it would go from something like this:

To something like this, which will only show the Ultimate Parent name if it was deemed as one of the two that we are searching for:

Is something like this possible with a quick change of the formula? I've been trying to toy around with it a bit myself and haven't reached the end game for what I'm trying to achieve quite yet.

Thanks again for the first response...I'll definitely be incorporating that sort of logic in my reports going forward regardless.

 

[dcu5001]

I've changed the entities that I want deemed as the "Ultimate Parent" IDs to say "Ultimate" (like in the table pictured below). I also changed the formula to the following:

=IFERROR(IF(VLOOKUP(C2,$A$2:$D$1000,4,0)="Ultimate",VLOOKUP(A2,$A$2:$D$1000,1,0),VLOOKUP(C2,$A$2:$D$1000,4,0)),"Ultimate")

but the new Ultimate Parent column is being erroneously populated (having entities which don't wrap up into one of the Ultimate Parents still being flagged as "Ultimate"). I've tried altering the script so that it returns a dash or something minimal if it doesn't wrap up into an Ultimate Parent, but I can't seem to get it right. Any sort of push in the right direction would be greatly appreciated.

 

[Jerry Sullivan]

If I'm understanding your description, this should work.

In D2 and copy down.
=IFERROR(IF(C2="Ultimate",B2,VLOOKUP(C2,$A$2:$D$1000,4,0)),"-")

If I've misunderstood please post an image that shows all four columns of the desired result including the top rows which are not shown in your previous images.

 

[konew1]

How many levels of hierarchy do you have?

 

[dcu5001]

Thanks again for the response...I really appreciate the help. I apologize, the last two posts I put were a bit convoluted, so I'll break it out in a more straightforward manner.

I'm going to start with a list of entity's that have their own ID and Parent ID fields as so:

Out of this list, let's say I want to deem Bank Headquarters 2 and 3 as "Ultimate Parents." I will go ahead and change their Parent IDs to "Ultimate" as so:

The last step I want to do is create a new column which will indicate which of the entities eventually roll up into one of the two Ultimate Parents, while ignoring the ones which do not. Please note that all entities won't wrap up into one of these Ultimate Parents.

Sorry for the confusion earlier, but hopefully this will clear things up.

 

[dcu5001]

I'm working with a couple thousand rows of data and when I was originally mapping out the hierarchy when I first started working with this data I got up to like 12 or 13 levels of Parent-Child relationships.

 

[konew1]

Insert 2 new rows of data into row 2.
A2 Ultimate
C2: Ultimate
A3: 1
C3:1
In D embed VLOOKUP 13 times
I don't have time to create the formula, but will do this time tomorrow if needed.

 

[Jerry Sullivan]

dcu5001, For your clarified description you could use: In D2 and copy down...

=IFERROR(IF(C2="Ultimate","Ultimate",VLOOKUP(C2,$A$2:$D$1000,4,0)),"-")

The formula I suggested in post #5 will list the Entity Name of the Ultimate Parent, which is probably more useful to you than just listing "Ultimate".

The numbers of levels should not be a factor in solving this since the cell in column D keeps track reports its ultimate parent and passes it on to any children regardless of how many levels down that row is.