Excel Create Hierarchy given level in order

Question

I have an order of unique Id's and the level of their indentation. They are in order so the parent of the current ID is the ID of the one with 1 less than the current indent level (e.g. if the current indent level is 3, the parent ID will be the id of the first row above it with an indent level of 3-1=2 which is UniqueID=4 in this case).

Here is a sample of my data:

I want a new column with the parent UniqueID of the current UniqueID.

Thanks

Answer 1

I figured it out. I wrote a Visual Basic script that contains the following:

Function parent(r As Range) As Integer

curVal = r.Value
desiredCol = 2

parentFound = False

counter = 1
'loop through until parent is found or at beginning
Do Until CInt(r.Row) < 2 Or parentFound = True
    If r.Offset(-counter, 0).Value < curVal Then
        parentFound = True
        desiredRow = r.Offset(-counter, 0).Row
    End If
    counter = counter + 1
Loop

parent = r.Offset(-counter + 1, -1).Value

End Function

This searches for the first cell above in the column with a level below it, and takes the value of the ID to the left of it.