Concatenate unique values among duplicates

[Toby]

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

 

[Toby]

Thanks, that looks like it will work, but I'm on a company laptop that blocks
me from installing any software, addons, etc. Is there any other way to do
this within the existing functionality of excel?

Toby

 

[Domenic]

Thanks, that looks like it will work, but I'm on a company laptop that blocks
me from installing any software, addons, etc. Is there any other way to do
this within the existing functionality of excel?

Toby

If you don't mind returning the locations in separate cells, I can offer
you an alternative formula solution. Otherwise, if you want them
returned in a single cell, you're going to need a VBA solution. Which
route would you like to take?

 

[Toby]

Thanks again for the quick reply. It wouldn't be a problem to return
locations in different cells, so a formula solution for that would be great.
I'll also talk to my IT folks to see if they'll allow me to install the excel
addon you referenced. Looks like a lot of great stuff there.

Toby

 

[Domenic]

Thanks again for the quick reply. It wouldn't be a problem to return
locations in different cells, so a formula solution for that would be great.

Based on the same assumptions as previously, try...

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

=SUM(IF(FREQUENCY(IF($A$2:$A$100=$E2,IF($C$2:$C$100<>"",MATCH("~"&$C$2:$C
$100,$C$2:$C$100&"",0))),ROW($C$2:$C$10)-ROW($C$2)+1),1))

G2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(COLUMNS($G2:G2)<=$F2,INDEX($C$2:$C$100,SMALL(IF(FREQUENCY(IF($A$2:$A$
100=$E2,IF($C$2:$C$100<>"",MATCH("~"&$C$2:$C$100,$C$2:$C$100&"",0))),ROW(
$C$2:$C$100)-ROW($C$2)+1),ROW($C$2:$C$100)-ROW($C$2)+1),COLUMNS($G2:G2)))
,"")

I'll also talk to my IT folks to see if they'll allow me to install the excel
addon you referenced. Looks like a lot of great stuff there.

....and more efficient, too.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No formulas
No add-ons
http://www.mediafire.com/file/hgzynii2izu/04_15_09a.xlsx