Hierarchy Parsing - Macro

[DataBlender]

Apologies in advance for the long-winded description but I'm not sure of a better way of explaining it.

I have an Excel input which contains a hierarchical structure of seven levels, together with the lowest level units, all within the same column. The delimination between the levels is by way of indentation which is lost upon input into Alteryx. I therefore have an Excel macro which counts the number of indents and returns this as a value in a new field, which becomes our Level n. e.g.

Item/hierarchy	Level (count of indents)
A	0
B	1
C	2
D	3
E	4
F	5
Item x	6
Item y	6
G	5
Item z	6

In the above example, Item x and Item y are the lowest level and have all hierarchical attributes of those above them. Item z shares the hierarchical attributes of  levels 1-4 but its level 5 is G. The expected result is:

Level 0	Level 1	Level 2	Level 3	Level 4	Level 5	Level 6
A	B	C	D	E	F	Item x
A	B	C	D	E	F	Item y
A	B	C	D	E	G	Item z

I think I have an adequate solution by way of multiple, multi-row formulae (one for each new field). My questions are:

- Is there a better way of achieving the same result?

- can I turn this into a batch macro (?) using the number of indents as a control parameter so that I can use this in my workflow rather than having numerous multi-row formulae?

Thanks

[Federica_FF]

Hi DataBlender,

I don't know if the attached solution is better or not, but it should be more dynamic. Using a couple of crosstab and transpose tool you can use 1 single multirow formula tool and if in the future your input will have 10 levels of hierarchies, you don't have to add N multirow formula tools, the workflow should work no matter the number of hierarchies levels.

The first MultiRow formula creates a ID number that restart from 1 when starts a new indent level (that indicates the number of final rows you'll have

If you pivot your table you can easily copy the value A, B, C, etc... vertically (like dragging down in excel) with a formula if isempty(value) then value row-1 else value

And then you can pivot everything again and get the final result.

[Joe_Mako · Accepted solution]

@Federica_FF your route has an error, "Item y" should have a Level 5 value of "F".

Attached is a similar route, that expects the outputted record to all be at the max Level.

It adds a Record ID, performs Cross Tab and Transpose, ensures Level is numeric, sorts, fills value, joins result to filter, keeping records that matches the overall max Level (Num_indents), Transpose, and renames.