I have a list as per below, where the A0001 item contains five components, and one of them, B0005, is broken down in to three components.
I would like to see it as a "tree view" where I can see the items needed for A0001 down to the lowest level.
Please note that B0005 can also be used in other products.
This is the kind of output I would like to get:
I did once manage to do it in PowerBI and then exported it back to Excel, but I can't remember how I did it and can't recreate it.
Any suggestions on how to do this with VBA?
I would like to see it as a "tree view" where I can see the items needed for A0001 down to the lowest level.
Please note that B0005 can also be used in other products.
Item number | Item name | Sub item | Sub item name | Quantity |
A0001 | Product A | B0001 | Box | 1 |
A0001 | Product A | B0002 | Screw 1 | 3 |
A0001 | Product A | B0003 | Screw 2 | 2 |
A0001 | Product A | B0004 | Motor | 1 |
A0001 | Product A | B0005 | Filterbox X | 1 |
B0005 | Filterbox X | C0001 | Filter | 1 |
B0005 | Filterbox X | C0002 | Tape | 2 |
B0005 | Filterbox X | C0002 | Locking screw | 4 |
This is the kind of output I would like to get:
Item number | Item name | Sub item | Sub item name | Item number Lvl 2 | Item name Lvl 2 | Sub item number Lvl 2 | Sub item name Lvl 2 | Quantity |
A0001 | Product A | B0001 | Box | 1 | ||||
A0001 | Product A | B0002 | Screw 1 | 3 | ||||
A0001 | Product A | B0003 | Screw 2 | 2 | ||||
A0001 | Product A | B0004 | Motor | 1 | ||||
A0001 | Product A | B0005 | Filterbox X | B0005 | Filterbox X | C0001 | Filter | 1 |
A0001 | Product A | B0005 | Filterbox X | B0005 | Filterbox X | C0002 | Tape | 2 |
A0001 | Product A | B0005 | Filterbox X | B0005 | Filterbox X | C0002 | Locking screw | 4 |
I did once manage to do it in PowerBI and then exported it back to Excel, but I can't remember how I did it and can't recreate it.
Any suggestions on how to do this with VBA?