Split indented Heirarchy list into columns

[schueyisking]

hi, I have a parent/child hierarchy list that I've exported out of our systems - I want to rearrange it so it goes from this:
EDIT: the paste didn't work, but basically INCOME has no indent, RENTS RECEIVABLE has 5 spaces indent, then the ones underneath it have a further 5 indents.


Display String
INCOME
Rents Receivable
Rent Receivable - Secure
Rent Receivable - Assured
Rent Receivable - Shared Ownership
Rent Receivable - Hostels (Licence)
Rent Receivable - Contractual Garages
Rent Receivable - Leasehold
Rent Receivable - Assured Shorthold
Rent Receivable - Ineligibles
Rent Receivable - Fair Rent
Rent Receivable - Contractual
Rent Receivable - Mixed Funding
Administrative Charges
Voids
Voids
Voids - Fair Rent
Voids - Contractual
Voids - Mix Funded
Voids - Garage


[TABLE="width: 521"]
<tbody>[TR]
[TD]INCOME
[/TD]
[/TR]
[TR]
[TD] Rents Receivable
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Secure
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Assured
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Shared Ownership
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Hostels (Licence)
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Contractual Garages
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Leasehold
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Assured Shorthold
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Ineligibles
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Fair Rent
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Contractual
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Mixed Funding
[/TD]
[/TR]
[TR]
[TD] Administrative Charges
[/TD]
[/TR]
[TR]
[TD] Voids
[/TD]
[/TR]
[TR]
[TD] Voids
[/TD]
[/TR]
[TR]
[TD] Voids - Fair Rent
[/TD]
[/TR]
[TR]
[TD] Voids - Contractual
[/TD]
[/TR]
[TR]
[TD] Voids - Mix Funded
[/TD]
[/TR]
[TR]
[TD] Voids - Garage
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 521"]
<tbody>[TR]
[TD]INCOME
[/TD]
[/TR]
[TR]
[TD] Rents Receivable
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Secure
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Assured
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Shared Ownership
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Hostels (Licence)
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Contractual Garages
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Leasehold
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Assured Shorthold
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Ineligibles
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Fair Rent
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Contractual
[/TD]
[/TR]
[TR]
[TD] Rent Receivable - Mixed Funding
[/TD]
[/TR]
[TR]
[TD] Administrative Charges
[/TD]
[/TR]
[TR]
[TD] Voids
[/TD]
[/TR]
[TR]
[TD] Voids
[/TD]
[/TR]
[TR]
[TD] Voids - Fair Rent
[/TD]
[/TR]
[TR]
[TD] Voids - Contractual
[/TD]
[/TR]
[TR]
[TD] Voids - Mix Funded
[/TD]
[/TR]
[TR]
[TD]
Voids - Garage


to this:
[TABLE="width: 573"]
<tbody>[TR]
[TD]Parents
[/TD]
[TD]Child1
[/TD]
[TD]Child2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Secure
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Assured
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Shared Ownership
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Hostels (Licence)
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Contractual Garages
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Leasehold
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Assured Shorthold
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Ineligibles
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Fair Rent
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Contractual
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Rent Receivable - Mixed Funding
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Rents Receivable
[/TD]
[TD] Administrative Charges
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids - Fair Rent
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids - Contractual
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids - Mix Funded
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids - Garage
[/TD]
[/TR]
[TR]
[TD]INCOME
[/TD]
[TD] Voids
[/TD]
[TD] Voids
[/TD]
[/TR]
</tbody>[/TABLE]


Any ideas please?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1227869-convert-indented-heirarchy-list-to-columns.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[manshah]

Can someone plz post the solution to this thread here.

 

[sandy666]

is that what you want?

RAW	Parent	Child1	Child2
Rent Receivable - Secure	INCOME	Rents Receivable	Rent Receivable - Secure
Rent Receivable - Assured	INCOME	Rents Receivable	Rent Receivable - Assured
Rent Receivable - Shared Ownership	INCOME	Rents Receivable	Rent Receivable - Shared Ownership
Rent Receivable - Hostels (Licence)	INCOME	Rents Receivable	Rent Receivable - Hostels (Licence)
Rent Receivable - Contractual Garages	INCOME	Rents Receivable	Rent Receivable - Contractual Garages
Rent Receivable - Leasehold	INCOME	Rents Receivable	Rent Receivable - Leasehold
Rent Receivable - Assured Shorthold	INCOME	Rents Receivable	Rent Receivable - Assured Shorthold
Rent Receivable - Ineligibles	INCOME	Rents Receivable	Rent Receivable - Ineligibles
Rent Receivable - Fair Rent	INCOME	Rents Receivable	Rent Receivable - Fair Rent
Rent Receivable - Contractual	INCOME	Rents Receivable	Rent Receivable - Contractual
Rent Receivable - Mixed Funding	INCOME	Rents Receivable	Rent Receivable - Mixed Funding
Administrative Charges	INCOME	Rents Receivable	Administrative Charges
Voids	INCOME	Voids	Voids
Voids - Fair Rent	INCOME	Voids	Voids - Fair Rent
Voids - Contractual	INCOME	Voids	Voids - Contractual
Voids - Mix Funded	INCOME	Voids	Voids - Mix Funded
Voids - Garage	INCOME	Voids	Voids - Garage

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Trim = Table.TransformColumns(Source,{{"RAW", Text.Trim, type text}}),
    Distinct = Table.Distinct(Trim),
    Income = Table.SelectRows(Table.FillDown(Table.AddColumn(Distinct, "Parent", each if Text.Contains([RAW], "INC") then [RAW] else null),{"Parent"}), each ([RAW] <> "INCOME")),
    Child1 = Table.FillDown(Table.AddColumn(Income, "Child1", each if Text.Contains([RAW], "Adm") then null else if not Text.Contains([RAW], "-") then [RAW] else null),{"Child1"}),
    Child2 = Table.SelectRows(Table.AddColumn(Child1, "Child2", each if Text.Contains([RAW], "-") then [RAW] else if Text.Contains([RAW], "Adm") then [RAW] else if Text.Contains([RAW], "Voi") then [RAW] else null), each ([Child2] <> null))
in
    Child2

 

[manshah]

Thank you so much!!

 

[sandy666]

You are welcome
Thanks for the feedback