Forum Discussion
Manchester05
Oct 11, 2023Copper Contributor
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
- You'd need VBA for that. I found a discussion here: https://www.mrexcel.com/board/threads/using-vba-for-smart-art.554224/
But if you're OK with VBA, perhaps you need a more sophisticated implementation, like a treeview?
https://jkp-ads.com/articles/treeview.asp
- PeterBartholomew1Silver Contributor
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)
- JKPieterseSilver ContributorDo 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).
- Manchester05Copper ContributorThank 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- JKPieterseSilver Contributor
Manchester05 Does the attached come close? It was created using Insert, SmartArt, horizontal hierarchy.
You change the level of an item by using Tab (indent) or Shift-tab (outdent)