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)