Creating indented BOM report w/ Sample

Hello all,

I am trying to create an indented bom report. I have been searching around and the best i could find was to do the opposite of what I want to do going from indented to flat.

I have attached a sample workbook with the way the data is formatted and how i would like to see it formatted.

In short there are two columns of criteria, Column B and column E.

Column B is "Top Level" and column E are components.

I want to use a macro to search column B for any of the values in column E for each PN and copy/paste it up as indented my expected output should make that more clear.

The furthest down I need to dig is 6 levels and i created 6 sub columns on the expected output tab.

If anyone can take a look and get something going I would appreciate it immensely.

Okay so if you look at the original output B is a component of top level A.

B has its own "Top Level" Structure meaning that it is a sub assembly which A consumes.

on the original you can see that AB is a component of B but again AB has a "top level" structure as it exists in column B on the original making is also a sub assembly which B consumes and so on and so forth.


The easiest way for me to think about it is a part exists in both column E and B it is a sub assembly as all of the items in column B have components listed to them.

I did not completely blow out the original on the expected just took a crack to get one level blown out for demonstration.

That helps.

Why is PI is rows 7-9 but then you skip down to row 15 before seeing it again? Why isn't it in row 10?

Because of where UT presented it self in the list since UT is a component of PI and also has a sub i broke apart UT at that level. Where GT, GY, and IO do not show up in column B of the original so I know they are a bottom level good such as a bolt

that being said there is no reason why it couldnt be reordered so that it would look something like this. which looks cleaner however with the original export UT could be listed 1st, 2nd, 3rd, or 4th for the component # of PI so for simplicity's sake breaking it apart however the original export is laid out is fine.

Control #	Top Level Number	Sub Number	Sub Number	Sub Number	Sub Number	Sub Number	Sub Number]	Description	Task #	Component Number	Task Description
	A									B
		B								AB
			AB							MT
				MT						PO
				MT						PI
					PI					GT
					PI					GY
					PI					IO
					PI					UT
						UT				RE
						UT				OK
							OK			QW
							OK			QE
							OK			QR

Anyone have a solution?

Perhaps this will somehow help.

Thats close I will play with the code to see if i can manipulate it to get it to do what I am after but i am not 100% sure where to start with what is written.

Havent been able to get it to do what I am after still if anyone else can take a look that would be great.