Search code examples
excelvbaexcel-2007

Determine the parent of a row excel

I have an excel sheet with the following format 

Name | Data | Level | parent |
------------|-------|--------|
AAA  | XXX  | 1     | root(?)| 
BBB  | YYY  | 2     | AAA(?) |
CCC  | ZZZ  | 3     | BBB(?) |
DDD  | XYZ  | 4     | CCC(?) |
EEE  | YZX  | 3     | BBB(?) |
FFF  | ZXY  | 4     | EEE(?) |
GGG  | ZXR  | 4     | EEE(?) |

what i'm trying to do is get the name of the parent for each row. for instance i for CCC i should be able to fill its parent column with BBB. any ideas how i can achieve this?

Solution

  • In D2 Enter 

    =IF(C2=1,"Root",LOOKUP(2,1/($C$2:$C2=C2-1),$A$2:$A2))

    Then drag down as needed? This works with your samples at least.

    Explanation is as follows:

    Test if value on this row in column Level = 1, it it does, return "Root".

    Test if value on this row in column Level =1, if it does not then do as follows:

    get array of all rows with a level one up from current rows, level. ($C$2:$C2=C2-1).

    Divide that array into 1, to get errors on every position that is not one level higher.

    Look up a 2 in the array of errors and 1's. (This returns the bottom most 1 (the last row one level higher))

    Return value in Name column at this position.

    Results should like as follows:

    enter image description here