An Excel concatenation question

Factual Questions
1

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?

2

Is there any way you can upload a sample file with the relevant sheets, either in Excel Online, Google Sheets, a shareable link to a file, etc.? It can have 100% fake data (as long as it’s in the same format).

Sorry, I read your description a few times and it was hard to make sure I understood it correctly. A sample file would be easier to understand, if it’s not too much trouble to make.

I’m not sure if I understood this right, but if you managed to get it into a format where the filtering is correct, but each row has a bunch of added columns for the categories, maybe you could hide those categories and TEXTJOIN() their values into a single cell.

It’d still be helpful to see an example file, though.

3

Thank you!

Have worked up some demo data.
Now just need to thunk out best way to share the file. :upside_down_face:

4

The OP sent me a sample file, and we got it mostly working with a formula like:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(Models!G:G, Models!D:D = B2))))

With @penultima_thule’s permission, I will post a sample workbook in case anyone else has better ideas.

5

[notworthy] Go for it! [/notworthy]

Looks like I was but one short step down the (much longer) right path! :upside_down_face:

Thank you!