UPDATE
I looked at: Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')
And it is clear that does not answer the problem. Note contents of the "pivot table" here are not values, as in a normal pivot table, but rather refernces (pointers) to a tree structure. It is only the leaf nodes that are content. So I don't believe that link will solve the problem here unless greatly modified.
I just tested their approach, and it is not working since this is a tree with embedded node links in the table.
We have a excel sheet that is being used as a 3-deep tree (one root, 3 levels) that is being used for data validation for list pul-downs (using the trick: =INDIRECT(H10) ). To do that we created a sheet that is of the format:
Root Tier1 Tier1a Tier1b Tier1c Tier2 Tier2a etc….
Tier1 Tier1a Tier1a0 Tier1b0 Tier1c0 Tier2a Tier2a0
Tier2 Tier1b Tier1a1 Tier1b1 Tier1c1 Tier2b Tier2a1
Tier3 Tier1c Tier1a2 Tier1b2 Tier1c2 Tier2c
Tier1b3 Tier1c3
Tier1c4
We need to turn this into a table that is flattened to 3 columns. Of the format:
Tier1 Tier1a Tier1a0
Tier1 Tier1a Tier1a1
Tier1 Tier1a Tier1a2
Tier1 Tier1b Tier1b0
Tier1 Tier1b Tier1b1
Tier1 Tier1b Tier1b2
Tier1 Tier1b Tier1b3
Tier1 Tier1c Tier1c0
Tier1 Tier1c Tier1c1
Tier1 Tier1c Tier1c2
Tier1 Tier1c Tier1c3
Tier1 Tier1c Tier1c4
Tier2 Tier2a Tier2a0
Tier2 Tier2a Tier2a1
etc. etc. etc.
How can one do this with the least effort. I.e. I probably can write from scratch a VBA macro that does a pre-order traversal (recursive) of the tree and pumps out the triples for each node. But, since this is a one-time operation, I don't care if it is a re-usable VBA or just some excel interactive magic.
