Finding parent in a hierarchy

[Mackay2m]

Hello,

I'm trying to find a simple solution to finding the parent value in a hierarchy type table of data. I've attached a sample of my data. For each cell, I need to know what the parent level is. There can and is a "one to many" relationship of parent to children.

If anyone has a solution to this, that would be great!

TIA.

[DavidBowman]

I have solved for this before but "simple" was not how it was solved. When you have a tree like this and you are trying to find a parent there are 3 approaches you can take.

If it is a consistent and standard tree where the parents are always in the correct column just to the left of the column you are testing for - you can try nested offset and match formulas to get to the parent. The challenge is looking up instead of down which is what has to happen in your data.

If it is more complicated than that you can use VBA to get there by reading each cell and testing for the first data cell above it that works or copying the blank parents down.

A newer option is to load the table into powerquery using the data tab and use that functionality to get the parent to cascade down the columns.

The best possible option is really to have the hierarchy tree export in such a way that the parents copy downward and that is one way to approach the macro version of this answer.

[Mackay2m]

Thanks David, any chance you can give an example of the offset/match formula?

If not, I'll have to try powerquery. Unfortunately, this data is a dump out of SAP into a tab doc.

[DavidBowman]

Attached is a file that demonstrates it in PowerQuery. Just make a PowerQuery table from the source data called source. Then make another table with the code below in the advanced editor to create the table with a parent.

Please Login or Register  to view this content.

[FlameRetired]

Formula solution try

Please Login or Register  to view this content.

[Mackay2m]

Thanks for the help

[Mackay2m]

What is the ("zzzz") representing?

[Mrrrr]

Formula solution try

Please Login or Register  to view this content.

Fyi this is array formula (use CTRL+SHIFT+ENTER to activate).

[FlameRetired]

What is the ("zzzz") representing?

It is a text string very likely to be have a "larger" value than anything in the growing range that follows behind. For example in cell J6 if you highlight just this part in the formula bar INDEX($A$1:$G6,,MATCH(1,--($A6:$G6<>""),0)-1) and hit the F9 function key you will see an array of 0s and a text value

{0;0;0;0;"NCC_RSCH";0}

Since LOOKUP attempting to match the text string "zzzz" it ignores the numbers and returns the left-most string in that array that is less than or equal to "zzzz". If it were numbers you were expecting LOOKUP to return you would use an appropriately large number instead of "zzzz".

Does that help?

Fyi this is array formula (use CTRL+SHIFT+ENTER to activate).

Actually it didn't require CSE at my end. Have you tried it? LOOKUP seldom require it.

[Mrrrr]

Yes, I've tried it and thought of CSE when it returned the #VALUE error.

[FlameRetired]

Hmm. It's working in the attached.

[Mackay2m]

Hmm. It's working in the attached.

Thanks! It works!

[FlameRetired]

Good deal. You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.