# An Excel concatenation question **URL:** \ **Category:** Factual Questions\ **Created:** [July 8, 2025, 11:06am UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626 "2025-07-08T11:06:35Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [July 8, 2025, 11:06am UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/1 "2025-07-08T11:06:35Z")
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? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [July 8, 2025, 11:26am UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/2 "2025-07-08T11:26:39Z")
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. > [@penultima\_thule](#): > > 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. 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()](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c) their values into a single cell. It’d still be helpful to see an example file, though. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [July 8, 2025, 1:34pm UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/3 "2025-07-08T13:34:21Z")
Thank you! Have worked up some demo data. Now just need to thunk out best way to share the file. 🙃 ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [July 9, 2025, 12:46am UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/4 "2025-07-09T00:46:07Z")
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. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [July 9, 2025, 1:35am UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/5 "2025-07-09T01:35:01Z")
[notworthy] Go for it! [/notworthy] Looks like I was but one short step down the (much longer) right path! 🙃 Thank you! ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [July 9, 2025, 4:29pm UTC](https://boards.straightdope.com/t/an-excel-concatenation-question/1020626/6 "2025-07-09T16:29:59Z")
You’re welcome! If you need any further tweaks (like a different sort order), let us know. I’m not sure which of your current columns refer to the “FOB value”. Here is the example spreadsheet with the new formula: [ContainersV2.xlsx - Google Sheets](https://docs.google.com/spreadsheets/d/12JkoMnZcNmZabKQMrOGeX6vpCol-mNV8/edit?gid=1701984165#gid=1701984165) (with some extra columns hidden to simplify the example) In the “Shipment” sheet, column G has the formula: ```auto =TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(Models!G:G, Models!D:D = B2)))) ``` [![](https://fightingignorance.org/b098130f-5518-49e6-b94f-362885ae2427-containersv2-xlsx-google-sheets-july-09-09-26-37-am.jpg) ](https://fightingignorance.org/b098130f-5518-49e6-b94f-362885ae2427-containersv2-xlsx-google-sheets-july-09-09-26-37-am.jpg) That points to the “Models” sheet, which has the same container IDs. Each row also has a category, and each container ID can have one or more rows/categories: [![](https://fightingignorance.org/ddc206b2-6088-4253-ac88-2413aeb50a32-containersv2-xlsx-google-sheets-july-09-09-28-08-am.jpg) ](https://fightingignorance.org/ddc206b2-6088-4253-ac88-2413aeb50a32-containersv2-xlsx-google-sheets-july-09-09-28-08-am.jpg) The formula just filters the `Models!G:G` by the matching container ID, extracts the `UNIQUE()` ones, `SORT()`s it, and then concats them all using `TEXTJOIN()`. No pivot table needed. Where you might need a pivot, or a more complex formula, is if you wanted to sum up some _other_ value to aid in sorting, i.e., calculating the sum of each container or category’s FOB value (still not sure which column that is) and then sorting the joined categories by that, per row in Shipment. It’s a lot simpler if you can live with just the alpha-numeric sorting that’s currently in place. * * * If anyone else has better ideas, please chime in!