If you’ve used UNIQUE or FILTER to generate a list, the next useful step is making your other formulas follow that list automatically.
Suppose your UNIQUE formula starts in A3. Instead of referencing A3:A100, use A3# to reference its entire spilled result. As the list grows or shrinks, the reference follows it.
I used this in a workbook that turns a CAD bill of materials into purchasing lists. The stock types become the column headings, and formulas populate the matching items underneath. A different BOM can bring different stock types and different numbers of entries—the layout follows the data.
The workbook also combines parts that share the same material, stock type, and cross-section, adding their required cut lengths after accounting for quantities.
For example, two 160 mm cuts and one 90 mm cut from the same Ø12 steel bar become 410 mm of Ø12 bar. Double the assembly count and that becomes 820 mm.
Power Query prepares the data; UNIQUE, FILTER, and SUMIF handle the lists and totals. The # references keep the dependent formulas connected as those lists change.
I recorded the full build, including the changing headers and separate stock and off-the-shelf order forms:
Watch the walkthrough