Oct 11 2023 01:23 AM
Oct 11 2023 01:23 AM
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
Oct 11 2023 02:40 AM
Oct 11 2023 04:35 AM
Oct 11 2023 05:18 AM
@C V Horie Possibly you could use the Outline feature with Grouping. It isn't visually that same tree structure but it does create the grouping/tree branching and even allows you to expand and collapse sections to focus on different areas. In this thread I also posted a macro that can automatically create that grouping based on a list of tier levels if that helps:
I also created a macro that creates a vertical flow chart/tree structure (part of a safety assurance case I'm working on) based on a pivot table. It is a bit more complicated but does demonstrate feasibility:
That is actually only the 'top' level and all the bottom boxes link to other tabs with their additional branches. But based on your link I really think the Outline feature is much closed to what you need and more functional.
Oct 12 2023 01:18 AM
Thank you. I was out at meetings yesterday.
The Smart looks useful, the Art is fairly redundant.
As far as I can see from the Smart Art help page, all the text needs to be typed into the text pane.
However, I am editing the databases daily (with > 2 million data points) which I should like the hierarchy to reflect.
Can the smart art tool import live data from, say, a spreadsheet? If so, what format and links does that database need to have?
Oct 12 2023 02:08 AMSolution
Oct 12 2023 03:59 AM
Oct 12 2023 05:06 AM
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)