Parent / Child Relationships

[JKwan]

I am wondering is there VBA solution to do a Parent / Child Relationships. Basically, I have levels from 1 thru 6 (may be more, rare occasion), given level 1 is the top most and it has no parent. Level 2 would be child of Level 1 and Level 3 would be child of Level 2 and so on. The end result, I would like a cell reference to the parent cell. The number of rows varies as well. I have a ton of these files and it gets quite time consuming to do this task.

Thanks

[p45cal]

In a cell in row 3 array-enter this formula:

=IF($B3>1,INDEX($C$1:$C$28,MAX(IF(B3-1=$B$1:$B3,ROW($B$1:$B3)))),"")
and copy down.
Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.
It assumes that column B is in ascending order for each section.

The row number can be obtained by using only this part of the formula, again array-entered, in any cell in row 3 and copied down:

=MAX(IF(B3-1=$B$1:$B3,ROW($B$1:$B3)))

Oops,
I would like a cell reference to the parent cell.then you need:
=IF(B3>1,ADDRESS(MAX(IF(B3-1=$B$1:$B3,ROW($B$1:$B3))),3,4),"")(array-entered).

[snb]

In Q6:


=INDEX($B$3:$B$17;MATCH(A6;$A$3:$A$17;0)-1)

[p45cal]

In Q6:


=INDEX($B$3:$B$17;MATCH(A6;$A$3:$A$17;0)-1)

Doesn't give the wanted result in row 16.

[Paul_Hossler]

What happens if a child (6139591) is repeated in different blocks at different levels with different parents?

25950

[JKwan]

Thank you p45Cal for the formula, it works great. snb, also thank you as usual. If p45Cal said that your formula gives the wrong result for row 16, I will then use his. Paul, asset number is unique, it does not repeat, unless the person enters it wrong

[snb]

It's only a matter of sorting per level first (structuring precedes coding).

[p45cal]

It's already sorted. Structure is in place.
There are. for example, multiple level 4 asset numbers, each of those level 4s may have its own children, level 5 asset numbers. Each level 5 asset number has one specific level 4 asset number, but which one? The one closest to it above it.