Create Flat BOM from indented BOM using macro/vba code

Hi,
I am trying to create a flat Bill of Materials (BOM) from an indented bill of materials.
The flat BOM of material should contain the list of all purchased parts required within the indented BOM.
My difficulty is trying to exclude lines on the indented BOM that belong to a purchased item.
I have attached a sample spreadsheet which contains an indented BOM and i have identified the lines i want to include in column A with explanation in column M.
When going through the BOM, if the item is MAKE, then the components of the make item should be included if they are purchased.
Basically my problem is trying to ignore the BOMs associated with purchased parts from the list.

In the sample spreadsheet, the item on line 9 (12620200) is purchased, however it has a BOM. I do not want to include the any of the parts within the BOM of 12620200 in my flat BOM.
in this example - i do not want to identify any part in lines 10 to line 30 in my flat BOM.
line 9 is at level 4 within the BOM - as its purchased - i want to ignore the next set of lines until i reach a part with a level less than or equal to 4 (1, 2, 3 or 4).
I want to apply this logic to all lines with the indented BOM.
A purchased item with a BOM can appear at any given level within the BOM.
I find the best method to understand the BOM structure is to look at one level at a time in sequence
Filter on levels 1 & 2 to see parent with relevant child, the look at levels 1,2 & 3. Then look at levels 1, 2, 3 & 4 and so on until you are looking at all levels.
Any help would be appreciated.

SampleBOM.xlsx

Hi, welcome to the forum

I think you forgot to attach the file?

ExcelForumError.JPGI am getting error message when I try to attach file

"underfinedYou do not have permissions to perform this action. Please refresh page and login before trying again"

It loops when I click ok - I have to kill internet explorer to get out of loop.

Thanks for the file.

FFiltering on Y/N produces a list that are all >=3 and BUY - is that what you want?

I have manually input entries into column A to replicate what I want to achieve.
unfortunately some of the purchased items have a BOM against them. I need to somehow hide these lines before I filter on the make/buy field.
For example, If I come across a buy item at level 3, I want to mark the lines beneath that line until I come to another item at level 1, 2 or 3. All the lines directly beneath it at a level higher 4,5,6,7 etc.. are components of the buy item and therefore I don't want to included them in my flat BOM.
You will see that some of the buy items on my flat BOM are at different levels.

So what would the rules be to selecting?

Yes, I want to filter on column A for "Y". I am looking for the macro/code to input Y/N into column A.
The BOM has multiple levels, starting at line 2, when I come to a BUY item I want to input "Y" in column A. However, if the BUY part contains components, I do not want to include the components of that BUY part, so if the BUY part is at level 3, i want to input "N" in column A until I get to the next part at Level 1, 2 or 3.

In the sample spreadsheet, the item on line 9 (12620200) is purchased, I have input "Y" into column A for this line. however, from lines 10 to line 30 represent the components of the part on line 9. I do not want to include the any of the parts within my flat BOM..
line 9 is at level 4 within the BOM - as its purchased - i want to ignore the next set of lines until i reach a part with a level less than or equal to 4 (1, 2, 3 or 4). So these lines have "N" in column A.
I want to apply this logic to all lines with the indented BOM.

In the sample spreadsheet, the item on line 9 (12620200) is purchased, I have input "Y" into column A for this line

why?

I have this so far...
IF(AND(D2<=3,E2="Buy"),"Y","N")
Changing <=3 to <=4 fixes row 9 and some others, but then, you decide to include 5?
What makes the rule change?

tuongtu3 no apologies needed - the more the merrier (maybe you can see something I missed)

Hi Haugheym,
Please try sample attached and hope it work. If it works then give big thank to "joe31623"

ps: line 218 in your sample is wrong. You said N but it should be Y

Regards,
tt3