In article ,
Toby wrote:

Hi. I'm trying to create a formula that, based on the results of a vlookup,
will combine any number of unique cell values into one.

In spreadsheet 1, I have all office location information rolled-up to 1 row
per location (Location 1 has x-square feet, x-rent, etc.)

In spreadsheet 2, I have all the detail for each location.
My data in spreadsheet 2 includes the following:

Location Expense Code LOB
AZ1 123 HR
AZ1 234 HR
AZ1 345 HR
AZ1 567 Finance

I have separate rows in my data for each expense code, many of which roll up
to the same LOB at the same location, resulting in duplicate LOB entries for
many of my locations. I need to create a vlookup from my summary spreadsheet
that will lookup each location in my data file, concatenate the unique LOB
values for each location into a single cell, so I can say Location 1 has HR,
Finance (not HR, HR, HR, Finance). Also, my data spreadsheet has approx. 250
locations with nearly 2000 rows, so a manual solution won't work. Any help
is greatly appreciated!

Toby

First, download and install the free add-in, Morefunc.xll...

http://xcell05.free.fr/morefunc/english/

Then, assuming that A2:C100 contains the source data, E2 contains the
first location of interest, E3 contains the second location of
interest,, and so on, try...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF($A$2:$A$100=E2 ,",
"&$C$2:$C$100,""),1)),", ","",1)

--
Domenic
http://www.xl-central.com