Multi-Level Bill of Material Automatic Grouping

Hi All,

Before I go off and try to write my own macro to do this (I'm not the best of VBA programmers) I was wondering if anyone has already solved this seemingly common problem. I have a multi-level bill of material exported from a database. The first column of this data shows the level of the entry. I would like to automatically group the data as shown. I've attached a sample sheet with some simplified data showing how it should be grouped.

The rules would be. Indexing down the rows
1. Look at the current level, call this START_LEVEL
2. If the level of the next row is greater then the current row level start a group
3. Continue to put items in this group until the level specified is back to START_LEVEL, do not put this item with the level=START_LEVEL in the current group

This would then have to be repeated again on the entries within the just created group since groups may exist within groups. I imagine doing this recursively until the next line is outside of the first group at which time you would start at the beginning of the above steps.

Starting List.jpg
Grouped List.jpg
BOM Auto Level.xlsx

Hi mwasserman,

thanks for your code. Helped alot!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Also, read the forum rules regarding code tags and update your forum profile - there is no Exceol 2020.