Forum Discussion

Manchester05's avatar
Manchester05
Copper Contributor
Oct 11, 2023

tree hierarachy chart

I want to create an hierarchy chart, comparable to the 1752 version below.

I have a growing collection of data points which can each categorised into an hierarchy position and level.  Is there a tool in Excel, or elsewhere, that can create such a tree from data in a database?  This is a common format in biological genetics, but I cannot find an MS program that achieves this.

This will be used to define, structure and access a web based data set.  At this stage, I want to use the visualisation to test and refine the data structure.

Top level: one point

2nd level: ca 4 points

3rd level: ca 3 points per level

4th level: up to 900 points per level (though this could be divided into 2 levels of ca 80 and 200 points per level)

5th level: up to 10 points per level

6th level: up to 20 points per level

 

Thank you

 

  • Manchester05 

    The conditional formatting is not the greatest but the basic tree is in place

    [I ignored the request for multiple rows because there were no section headings and there was a clash with subsequent numbering]

    = LET(
        level, 1 + QUOTIENT(LEN(section),2),
        prior, DROP(VSTACK(0, level),-1),
        row, SCAN(1, level>prior, LAMBDA(acc,s, acc+1-s)),
        location, row & "," & level,
        tree, MAKEARRAY(9,7, LAMBDA(r,lv, XLOOKUP(r&","&lv, location, LEFT(heading,30),""))),
        tree
      )

    though in use it would be

    = Treeλ(section, heading)

     

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Do you have some sample data for us to go on please? Preferably with a picture of the desired outcome. We don't need much, just data for three levels of items with at least one item for each level (preferably more than one).
    • Manchester05's avatar
      Manchester05
      Copper Contributor
      Thank you for your interest.
      This is sample of my data and suggested output
      "Studiorum proposed draft structure

      1. Home page

      1.1 Data Standards
      1.2 About
      1.3 Acknowledgements
      1.4 Search

      2.1 Catalogues
      2.1.1 Liber Studiorum (Finberg)
      2.1.1.1 Frontispiece (82 at this level)
      2.1.1.1.1 Etching (6 at this level)
      2.1.1.1.1.1 Engraver (ca 20 at this level)
      2.1.1.1.1.1.15 Digital image ref (ca 2 at this level)
      2.1.1.1.2 1st state
      2.1.1.2 Bridge and cows
      2.1.2 Engraved Works (Rawlinson) (87 at this level)
      2.1.2.1 The Copper plate Magazine (80 at this level)
      2.1.2.1.1 Rochester (16 at this level)
      2.1.2.2 The Pocket Magazine
      2.1.2.2.1 The Tower of London (16 at this level)"

      An example of the formatting of a tree (I cannot add a screen shot) is at
      https://www.ncbi.nlm.nih.gov/Taxonomy/Browser/wwwtax.cgi?mode=Undef&name=Eukaryota&lvl=3&srchmode=1&keep=1&unlock
      Velson

Resources