Hello All,

New to the forums and first time posting. I've hit a wall with developing a tiered Pivot Table to show a bill of materials. I have a solid understanding of Excel and basic VBA knowledge. Hoping someone on this forum can help...

I’m trying to take a large dataset (20,000+ rows) and convert it to a nice looking, easy to use table. Below is what the raw data looks like, along with the output I’d preferably like to see. Basically, each FG has underlying components, each with quantities and costs. Some of those components also have underlying components with the same info. I’d like to create some sort of way to pick a FG out of the data set, and view each level as desired (so in this instance you'd selecte 50 as the FG, and then either level 1, 2, or 3 to look at). The first level should always be visible, and any additional level that contains data should be reflected underneath the parent, but the parent level should not reflect the cost – only the most recent child level should. If the parent has no sub-components, only that line should be visible. I haven’t been able to build this out without including empty sub-levels or parent costs. As an FYI, this data goes 5 levels deep, and I am not able to use Access. Please don’t hesitate to ask any questions. Any help or suggestions would be appreciated. Spreadsheet attached if that helps as well. Thank you!

Raw Data
FG Level1 Qty1 Cost1 Level2 Qty2 Cost2 Level3 Qty3 Cost3
50 23-8056 0.5000 1.6000 66508 1.0075 1.0000 80900 0.5500 0.2500
50 23-8056 0.5000 1.6000 66508 1.0075 1.0000 80411 0.1500 0.2500
50 23-8056 0.5000 1.6000 66508 1.0075 1.0000 81051 0.3000 0.2500
50 56009 0.7500 0.7500
50 8151119 1.0000 2.0000
50 3012290 18.0000 0.5000

Output (level 1)
FG 50 (filter in pivot table)

LVL Item Qty Cost
1 23-8056 0.5000 1.6000
1 56009 0.7500 0.7500
1 8151119 1.0000 2.0000
1 3012290 18.0000 0.5000

Output (level 2)
LVL Item Qty Cost
1 23-8056 0.5000 0.0000
2 66508 1.0075 0.5038
1 56009 0.7500 0.7500
1 8151119 1.0000 2.0000
1 3012290 18.0000 0.5000

Output (level 3)
LVL Item Qty Cost
1 23-8056 0.5000 0.0000
2 66508 1.0075 0.0000
3 80900 0.5500 0.2500
3 80411 0.1500 0.2500
3 81051 0.3000 0.2500
1 56009 0.7500 0.7500
1 8151119 1.0000 2.0000
1 3012290 18.0000 0.5000
BOM example.xlsx