Convert Indentured List to Table

[runnerD2016]

For the attached excel file, is there a macro that could convert the indentured list in column B to the table on the right (E2:I11)? The tiers are determined based on the number of tabs used in the cells in column B. For instance, "apples" should fall under tier 4 and should be associated with "food" (tier 1), "natural" (tier 2), and "fruit" (tier 3).

Also, the macro (or a separate macro) should convert in the other direction as well (from the table to the indentured list).

Any thoughts?

[PCI]

First macro

Please Login or Register  to view this content.

[runnerD2016]

Thanks, this seems to work for up to 5 indents.

Is there a way to have the macro work so that it can handle an infinite (or very large) number of indents and show tier 1, tier 2, ... tier n (where n is the largest # of indents used) at the top?

[PCI]

See next code
N.B. for second macro wait tomorrow ...!

Please Login or Register  to view this content.

[runnerD2016]

Thanks for all the help. This works great for converting to the table format. I made a small modification with the headers:

Please Login or Register  to view this content.

[PCI]

Or just do

Please Login or Register  to view this content.

[PCI]

The second macro

Please Login or Register  to view this content.

[runnerD2016]

Thanks! This macro does everything that I need. I appreciate the help

[PCI]

Good news
Enjoy Excel