Create a tree structure in Excel - Possible VBA input

[Rav_Singh]

Hello all,

*Second time posting as the last thread did not display correctly*


This may be a long shot but I am throwing this out there to see if anyone can help. I would like to recreate a form of a three-structure in Excel.


Below is an sample set of folder structure data.

Commercial Litigation\Know-how
Commercial Litigation\Know-how\Court Documents & Guidance
Commercial Litigation\Know-how\Court Documents & Guidance\Disclosure Pilot - January 2019
Corporate Finance & Trust Acttu
Corporate Finance & Trust Acttu\Precedents and Non Function
Corporate Finance & Trust Acttu\Precedents and Non Function\Competition
Corporate Finance & Trust Acttu\Precedents and Non Function\Corporate Governance Trust
Corporate Finance & Trust Acttu\Precedents and Non Function\Corporate Governance Trust\Formation, Constitution
Corporate Finance & Trust Acttu\Precedents and Non Function\Corporate Governance Trust\Formation, Constitution\CA 2006


Ideally I would like the above example to transform into the below structure.

[TABLE="width: 929"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Commercial Litigation\ Know-how[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]> Court Documents & Guidance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]> Disclosure Pilot - January 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Corporate Finance & Trust Acttu[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]> Precedents and Non Function[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]> Competition[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]> Corporate Governance Trust[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]> Formation, Constitution[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]> CA 2006[/TD]
[/TR]
</tbody>[/TABLE]

The key characteristics would be to;


1) Remove the duplicate folder location text (if same as above cell) but have space instead
2) Add the character '>' at the start of the new folder location text. The '>' should start at the end of where the text ended in the above cell.


I presume this can only be done in VBA.


I hope this makes sense.


Any help would be greatly appreciated.


Rav