Search code examples
excelexcel-formula

Excel Dynamically Expanding Table

I am using Office 365 Excel.

Here are some terms for better understanding:

  • Bill of Materials = "BOM" for short, this is the instructions that are used to assemble Components into a Assembly or Finished Good item.
  • Finished Good Item = An item that is ready to sell to the customer (Will not be used as a Component item)
  • Component Item = An item that is used in building/assembling a Finished Good item (can be either a Assembly or a Base Item)
  • Assembly Item = An item that is not purchased/procured, may be a Finished Good or a Component Item
  • Base Item = A item that is not assembled, something that is purchased/procured (The very beginning of the Assembly process)

I have a spreadsheet with all the BOMs our company uses (around 19000~ items between Finished Goods and Components). On this spreadsheet, you have the Assembly item, followed by its Component items and the Quantity of each Component that is needed (to make a single Assembly item), each BOM is a single row of our table.

However some BOMs have Assembly Components, which have their own BOMs and those BOMs can also have Assembly Components. These Assembly items can go several layers deep, meaning you may have 3 or 4 BOMs nested within a single Finished Good item.

Now, what I am hoping to do is create a dynamically expanding table in Excel that includes all Assembly Component BOMs in the Finished Good items BOM (replacing the Assembly Component with its BOM), that way we don't have to reference multiple BOM rows and our procurement department can more quickly decide what Base items they need to order.

Here is an example of 3 BOM rows (From Finished Good 10086663 to Base Item 381550-P) that would combine:

Assembly Finished Good Component 1 Quantity 1 Component 2 Quantity 2
10086663 True 381550-ION 1
381550-ION False 381550-MS 1 OM-381550-ION 1
381550-MS False 381550-P 1 OM-381550-MS 1

Now here is what I would like this to look like (all 3 BOMs in 1 row, replacing the Assembly Component(s) with their BOM Components):

Assembly Finished Good Sub Sub Component 1 Sub Sub Quantity 1 Sub Sub Component 2 Sub Sub Quantity 2 Sub Component 2 Sub Quantity 2
10086663 True 381550-P 1 OM-381550-MS 1 OM-381550-ION 1

I'm adding "Sub" to the header to help display how many BOMs deep the Component is.

To clarify, I'm creating a fresh table on a new sheet. I want to replace the Assembly Component item with its BOM row. This can be nested several BOMs deep and will end up adding quite a few columns to the table. I provided one of our shortest examples, because these BOM rows can get quite long (our largest BOM has 30 component items (some Assembly, some Base items)).

I wish I knew the first place to start with something as complicated (to me) as this is. I can't think of a dynamic way to do what I'm looking for without manually entering a majority of the data...

On my new sheet I am using the following formula to display all the Finished Good Assembly items:

=FILTER(Table1[Assembly],Table1[Finished Good])

I figured I could use something like this formula to replace a single component with its BOM:

=CHOOSEROWS(Table1,MATCH(B2,Table1[Assembly],0))

But then I get a spill error if there is more components (which would be to the right of Component 1).

Next I tried nesting Index/Match to display a single component at a time (very tedious), but I'm not sure how to go back a step (without manually editing the formula) if there is 1 less BOM:

=INDEX(Table1,MATCH(INDEX(Table1,MATCH(INDEX(Table1,MATCH(INDEX(Table1,MATCH(A2,Table1[Assembly],0),MATCH($B$1,Table1[#Headers],0)),Table1[Assembly],0),MATCH($B$1,Table1[#Headers],0)),Table1[Assembly],0),MATCH($B$1,Table1[#Headers],0)),Table1[Assembly],0),MATCH($B$1,Table1[#Headers],0))

I've been racking my brain on this for a while now and can't think of a way to do this without manually entering a majority of the data I want. I would greatly appreciate assistance on this, and I'll provide a larger example below:

Before Combining:

Assembly Finished Good Component 1 Quantity 1 Component 2 Quantity 2 Component 3 Quantity 3 Component 4 Quantity 4 Component 5 Quantity 5
71K2628MLGBLT501501 True 11130FCT26M-MS-MS 1 R0995 1 R0906 1 R0428 1 GLD02-WH 2
11130FCT26M-MS-MS False 11130-1212-MS 1 11F12-MS 2 11BP02 2 R1225 2 R1008 2
11130-1212-MS False KFGEN370C-1212-MS 1 OM-11130-1212-MS 1 OC-FAB$-DRILL 1.5
11F12-MS False 11F12 1 OM-11F12-MS 1

After Combining:

Assembly Finished Good Sub Sub Component 1 Sub Sub Quantity 1 Sub Sub Component 2 Sub Sub Quantity 2 Sub Sub Component 3 Sub Sub Quantity 3 Sub Sub Component 4 Sub Sub Quantity 4 Sub Sub Component 5 Sub Sub Quantity 5 Sub Component 6 Sub Quantity 6 Sub Component 7 Sub Quantity 7 Sub Component 8 Sub Quantity 8 Component 9 Quantity 9 Component 10 Quantity 10 Component 11 Quantity 11 Component 12 Quantity 12
71K2628MLGBLT501501 True KFGEN370C-1212-MS 1 OM-11130-1212-MS 1 OC-FAB$-DRILL 1.5 11F12 1 OM-11F12-MS 1 11BP02 2 R1225 2 R1008 2 R0995 1 R0906 1 R0428 1 GLD02-WH 2

I appreciate any assistance!

Edit! There are 32 columns total (Assembly, Finished Good, [Component, Quantity] 1-30) as our largest BOM has 30 components. Also, these are in alphabetical/numerical order by Assembly (not sure if this helps). Apologies for any confusion!

Solution

  • Here is the final solution that achieved what I was looking for:

    =LET(
a,F2,
b,G2,

phco1,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],a)))*(Table1[Source]<>"STOCK")),""),
phco2,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],phco1)))*(Table1[Source]<>"STOCK")),""),
phco3,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],phco2)))*(Table1[Source]<>"STOCK")),""),

phqu1,IFERROR(FILTER(Table1[Quantity]*b,(ISNUMBER(XMATCH(Table1[Assembly],a)))*(Table1[Source]<>"STOCK")),1),
phqu2,IFERROR(FILTER(XLOOKUP(Table1[Assembly],phco1,phqu1)*Table1[Quantity],(ISNUMBER(XMATCH(Table1[Assembly],phco1)))*(Table1[Source]<>"STOCK")),""),
phqu3,IFERROR(FILTER(XLOOKUP(Table1[Assembly],phco2,phqu2)*Table1[Quantity],(ISNUMBER(XMATCH(Table1[Assembly],phco2)))*(Table1[Source]<>"STOCK")),""),

stco1,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],a)))*(Table1[Source]="STOCK")),""),
stco2,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],phco1)))*(Table1[Source]="STOCK")),""),
stco3,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],phco2)))*(Table1[Source]="STOCK")),""),
stco4,IFERROR(FILTER(Table1[Component],(ISNUMBER(XMATCH(Table1[Assembly],phco3)))*(Table1[Source]="STOCK")),""),

stqu1,IFERROR(FILTER(Table1[Quantity]*b,(ISNUMBER(XMATCH(Table1[Assembly],a)))*(Table1[Source]="STOCK")),1),
stqu2,IFERROR(FILTER(XLOOKUP(Table1[Assembly],phco1,phqu1)*Table1[Quantity],(ISNUMBER(XMATCH(Table1[Assembly],phco1)))*(Table1[Source]="STOCK")),""),
stqu3,IFERROR(FILTER(XLOOKUP(Table1[Assembly],phco2,phqu2)*Table1[Quantity],(ISNUMBER(XMATCH(Table1[Assembly],phco2)))*(Table1[Source]="STOCK")),""),
stqu4,IFERROR(FILTER(XLOOKUP(Table1[Assembly],phco3,phqu3)*Table1[Quantity],(ISNUMBER(XMATCH(Table1[Assembly],phco3)))*(Table1[Source]="STOCK")),""),

IFNA(TOROW(HSTACK(
IFNA(VSTACK(
stco1,stco2,stco3,stco4
),""),
IFNA(VSTACK(
stqu1,stqu2,stqu3,stqu4
),""))),""))