I have a spreadsheet with a large list of medications and the areas where they are kept. Each row shows a different area.

I want to remove all the duplicate values for the medications, but have all the areas combined into 1 cell separated by commas.

It might be a lot to ask, but can anyone help with the best way to do this

It will make the list a lot shorter (which I need), but will have all the locations together



I have attached an example of what I am after

Attached Files

example.xlsx (9.7 KB, 5 views)

It's possible, but do you really want to do this in such a way?

To do this you need a helper column in your original table, which calculates for every row the occurrence number (1, 2, 3, ... etc.) for current medication there. And then in other table you need a formula which reads the max occurrence number for particular medication, and concatenates x times the area of every occurrence of it in case this occurrence number is less or equal of max occurrence for this medication, or adds an empty string in case in case this occurrence number is greater of max occurrence for this medication. And the number of concatenations must be egual with greatest max occurrence number in whole table (i.e. x I used above) . The formula will grow huge very fast when at least one of medications may be present in many areas.

Why do you not simply:
use autofilter on your original table to get all locations of specific medication;
or order your table by medications, so all locations for every medication are grouped together. (And order them by locations in case you need all medications in every location to be grouped together)

Use Power Pivot and create a measure as shown in the attached file. Then look at this link for a tutorial on how this was created.
Excel: Reporting Text in a Pivot Table | IMA

Attached Files

example (1).xlsx (77.2 KB, 1 views)

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2511