Excel Input Data Prep - Condensing a hierarchy format

[JBLove]

Hi - I am consolidating an Excel workbook spanning multiple tabs with data containing varying levels of hierarchy.

While I've got the Macro figured out in sourcing the data, I am struggling with collapsing the hierarchy structure into the desired format.

Here's an example of what the data looks like:

F1	F2	F3	F4	F5
1.	Interest Income:
	a.	Interest and fee income on loans:
		(1)	In domestic offices	XXX
	b.	Income from lease financing receivables		XXX

This is what I would like it to look like:

F1	F2	F3	F4
1.Interest Income	a.Interest and fee income on loans:	(1) In domestic offices	XXX
1.Interest Income	b.Income from lease financing receivables	[null]	XXX

Any suggestions are greatly appreciated.

[DavidP · Accepted solution]

It was a bit of a struggle but I got there in the end. Not sure if the logic is robust enough.

[JBLove]

@DavidP  -

Thank you!  This is what I needed to get going in the right direction.