Hi all. My first post, so apologies if this is listed somewhere else. Honestly, I'm not sure how to search what I'm looking for. But here goes...
I have several worksheets with BOMs (Bill of Materials) that identify line items as numbers, with subparts including a decimal. If it is a parent level part, it has no decimal and the quantity acts as a multiplier for the subparts, or if there are no subparts is the quantity of the item itself.
For example, A2 below is line item "10" as a quantity of 1 as shown at B2. But that means the 1 is used as a multiplier for A3 and A4. That's very important for items like A10 where the multiplier is 2, and therefore the total quantity for item A11 is 8 (B10 x B11 = 2 x 4 = 8). But sometimes there is no subpart (see A9; "30", but no "30.1") so the quantity shown in column B is no longer a multiplier, but the quantity itself (i.e. A9 quantity is truly 2).
So the question is, how do I extract the multiplier to apply it to the subparts, but if there is no subpart still recognize it as a quantity for the parent part. And if there is a subpart, make sure I don't also show the parent multiplier as a quantity.
Annotation 2019-09-17 093753.png
A B C
1 Item Qty Output
2 10 1
3 10.1 2
4 10.2 2
5 20 1
6 20.1 1
7 20.2 1
8 20.3 4
9 30 2
10 40 2
11 40.1 4
12 50 2
13 60 3
14 70 4
15 70.1 1
16 70.2 2
I hope I'm explaining this clearly. It's trickier than what I'm used to working with.
Thanks in advance!
Bookmarks