A simple manufacturing concept where specific parts / sub assemblies comprise of a “build of material”.
I have this setup using logic in python and integrated into an ERP system but I am struggling to do the same thing or a simplified version in Excel. I am open to a better method but I am limited to excel formulas (non office 365). I do not want to use power query or something that is less navigable for the non technical.
In essence, there is a selection of options.
| OPTIONS | SELECTION |
|---|---|
| CRUST | X |
| SAUCE | A |
| TOPPINGS | O |
I created a massive list translating these options into Part / Qty / and basically if statements if they need to be included based on the option selections.
TABLE 1 – I AM AT THIS POINT
| PART | QTY | IN BUILD |
|---|---|---|
| CRUST X | 0.1 | 1 |
| FLOUR X | 0.2 | 1 |
| WATER X | 0.4 | 1 |
| EGGS X | 0.5 | 1 |
| CRUST Y | 0.5 | 0 |
| FLOUR Y | 0.5 | 0 |
| WATER Y | 0.5 | 0 |
| EGGS Y | 0.5 | 0 |
| SAUCE A | 0.5 | 1 |
| SAUCE B | 0.5 | 0 |
| SAUCE C | 0.5 | 0 |
| SAUCE D | 0.5 | 0 |
| TOPPINGS L | 0.2 | 0 |
| TOPPINGS M | 0.3 | 0 |
| TOPPINGS N | 0.1 | 0 |
| TOPPINGS O | 0.4 | 1 |
NOW THE QUESTION IS – HOW CAN I TURN THESE INTO A SINGLE DYNAMIC LIST USING A FORMULA (WITHOUT OFFICE 365).
THERE ARE DUPLICATES IN THE PART – IN A PERFECT WORLD THESE ARE SUMMED (IF NON ZERO)
TABLE 2 – I CANNOT FIGURE OUT HOW TO CREATE
| PART | QTY |
|---|---|
| CRUST X | 0.1 |
| FLOUR X | 0.2 |
| WATER X | 0.4 |
| EGGS X | 0.5 |
| SAUCE A | 0.5 |
| TOPPINGS O | 0.4 |
Scouring the internet I found a few relatable functions using index , aggregate, and array formulas but wasn’t able to really fully comprehend enough to adapt to my situation.