CODEWITHSUNDEEP
exceltreeparent-childnodes
Laurent Vonach
Laurent Vonach

Reputation: 1

Excel: Parent Child tree view: Add dynamically node (parent) numbers

I am working on a keyword tree for some Digital Asset System. Basically I have to import it in this format (mandatory):

1,0,Top Level A
2,0,Top Level B
3,0,Top Level C
4,1,Sub Level A
5,1,Sub Level B
6,4,Deepest Level A
7,5,Deepest Level B
8,3,Sub Level from Top Level C

Which in my case looks like this (this is an excerpt and I added some blank spaces to make it more readable):

 1,     0,    Natural and Organic Cosmetic (NOC)
 2,     1,    bodycare
 3,     1,    facecare
 4,     1,    babycare
 5,     1,    pregnancy
 6,     1,    lipcare
 7,     1,    hair
 8,     1,    teeth
 9,     1,    wellness
10,     1,    mencare (specific)
11,     0,    Model
12,    11,    application without product
13,    11,    application with product
14,    11,    adult
15,    14,    man
16,    14,    woman

But this list hast to be maintained and update regularly, and in order to ease the process I have created an Excel file where very column defines the level depth like this :

 1     0    Natural and Organic Cosmetic (NOC)
 2     1        bodycare
 3     1        facecare
 4     1        babycare
 5     1        pregnancy
 6     1        lipcare
 7     1        hair
 8     1        teeth
 9     1        wellness
10     1        mencare (specific)
11     0    Model
12    11        application without product
13    11        application with product
14    11        adult
15    14            man
16    14            woman

Now my problem is that I would like to add much more sub-level items let's say under "body-care". As a result I have to manually update all parents id number which are unfortunately abased on the unique and sequential identifier in the first row.

How can I dynamically update the parent node ID number when I add a new row?

Upvotes: 0

Views: 2023

Answers (1)

hsan
hsan

Reputation: 1559

This solution will only work with a fixed depth of the tree. So if you want to add a level 3 node you will have to expand the formula accordingly...

Also you will have to drag/copy the formulas in columns A and B down whenever you insert a new row. Unless you can use tables (Excel 2007 and more recent versions) where formulas will be automatically added if they are the same for the whole column.

OK. The idea is to check what level of depth we are in and finding the first (upward looking) entry in the parent level. Not very elegant but it works.

In column A you simply use the formula =ROW()-1 to get the "unique sequential node numbers".

For column B (Parent Node Number) you can use LOOKUP like this:

=IF(C2<>"",0,IF(D2<>"",LOOKUP(2,1/(C$2:C2<>""),A$2:A2),IF(E2<>"",LOOKUP(2,1/(D$2:D2<>""),A$2:A2))))

The condition (C$2:C7<>"") will return an array like the following with 1 for a match and 0 for no match:

{0;0;1;0;0;0;1;0}

Dividing 1 by that array will result in an array like this: 

{#DIV/0!;#DIV/0!;1#DIV/0!;#DIV/0!;#DIV/0!;1#DIV/0!}

If you use LOOKUP with a lookup value greater than any of the values in the range it will return the last numerical value which in this case is the last 1 of the last match.

Upvotes: 1

Related Questions