I work in Supply Chain of a FMCG business.
We have a listing of inbound purchases as recorded in our ERP which is distributed with an Excel workbook.
The workbook has a models sheet, a simple listing of open purchase line item details. The items are imported by FCL (container). There might be several dozen line items/SKUs in each FCL. Each SKU belongs to a product category. The business distributes about 50 product categories. Each FCL may have SKUs from several categories, but typically less than 4.
The second sheet is shipments which summarises the purchases line items by FCL which are tracked and other import admin stuff.
For each row on the shipments tab, one of the columns lists the product categories within each FCL
e.g.
Container . . . . . . . . CATs
OOCL123456789 . . PC4, PC3, PC6
The product categories within each container are listed in decreasing FOB value, though alphabetically would do.
I get this done with a pivot table from the model sheet by FCL by category, displayed by decreasing FOB. Concatenate the categories listed, bring the text value into the shipments tab with a VLOOKUP.
Bit clunky, but it works.
Am looking for an alternative, neater method to achieve an equivalent result with formulas if feasible.
I was looking at using something like UNIQUE and FILTER, but the unique values for each row/FCL need to be displayed in a single cell, not a dynamic column list.
Assuming that description makes sense, would anybody know a cunning way of achieving this?