Create an Auto Fill, Cascading Table

Question

Hello! I am trying to create something a little more complex than I am use to, and make an auto generating Bill of materials list, based on a given part number.&nbsp;I currently have a table I'm trying to generate for such, as well as a separate sheet containing 15k lines of sub assemblies. With this I'm able to use Index to fill in Column A with a set of sub assemblies made from our initial component part, giving us:&nbsp;ItemNumber &gt; SubAssemblyA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyD&nbsp;I want to continue this path, re-iterating the function in Column C, and again in D, each based on the previous columns findings, so that we may see all end level items derived from our initial ItemNumber, as well as all of their sub assemblies.&nbsp;My problems starts here. Since Each Sub Assembly might build into multiple sub assemblies itself. I'm not sure how to go about this from here. I can't picture how to have column C and D auto insert rows, and copy down the formula until no items are find, for each item available from the previous col.&nbsp;The end result should look as followed, given that we are operating in BomTable:ItemNumber &gt; SubAssemblyA &gt; SubAssyAA &gt; FinalItemA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyB&nbsp;&gt; SubAssyCC &gt; FinalItemB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt; SubAssyXX &gt; FinalItemC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt; FinalItemX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt; FinalItemU&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt; SubAssyGG&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SubAssemblyDAlso to note, not all of these have final items, so some may just dead end.&nbsp;Sorry for the vagueness here as well, I imagine we could start with a table with a single cell, have it run a formula or code to index all parts for Column B, based on A1, then insert a new row each time we find an item, then do the same for column C based on the results of each row of B...? Except, while populating column C, we would also need it to determine if there's no result in the corresponding B cell, then follow up to see what the last non blank was here?Any help to make this more clear would be appreciated. Thank you!

patrick2788 · Answer

ShadowFeng&nbsp;It's possible with an array formula but the simplest solution may be the best. Have you considered a PivotTable?

shadowfeng · Answer

Patrick2788&nbsp;I'm still largely unfamiliar with them unfortunately. While playing with them for a short while, I can see how this might display the data I want, though I can't determine how to actually structure this, or how to organize the data for it to use properly. Looking further into it, but no luck so far.

patrick2788 · Answer

ShadowFeng&nbsp;A sample workbook would help tremendously (or even a small sketch of the data arrangement).

shadowfeng · Answer

Patrick2788&nbsp;Of course, sorry just felt I should avoid it if possible, should be fine though.&nbsp;Quick breakdown, one of my main component screens is on the left (014's), filtered to a given P/N, I am seeking to have cell A4 cascade out to show where all this P/N will go.Center screen is a massive list of all Parts, and their sub-assemblies, many of which may break down several layers, such as a 125- end item in Col A having a sub component related in Col D. That sub itself may also appear further down in Col A, with another sub, or part number related to it. This is broken down a little visually with some filtered searches of this list on the right side, showing this 014 going into 4 different subs, taking just one of those and showing it going into three others, then one of those into four, and one of those into our 4 end levels.&nbsp;I can somewhat split out the Bom list (middle) into a few different tables, but this becomes muddied quickly, as some parts will only have 1 level, such as 010's going right into the end levels, versus our 014 example with several different branching paths.&nbsp;That all being said, from what I played with the pivot tables yesterday and in the past, I can imagine it working for a nice format visually to show this, but i'm unsure if it can loop back over columns A and D like I would need it to do, which leaves me to believe that a table filled with index formulas reference the findings of the previous column, still seems the better option. Unless the array formula makes that all easier...

jonas-g · Answer

I have the same problem, ShadowFeng have you found a solution? As far as I can tell, a pivottable doesn't work.Patrick2788 How would the array formula look like

Forum Discussion

Create an Auto Fill, Cascading Table

5 Replies

Resources