Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 14th 09, 04:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 19
Default Concatenate unique values among duplicates

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

  #2   Report Post  
Old April 14th 09, 05:08 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 265
Default Concatenate unique values among duplicates

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
  #3   Report Post  
Old April 14th 09, 06:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 19
Default Concatenate unique values among duplicates

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" wrote:

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

  #4   Report Post  
Old April 14th 09, 07:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 265
Default Concatenate unique values among duplicates

In article ,
Toby wrote:

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?

--
Domenic
http://www.xl-central.com
  #5   Report Post  
Old April 14th 09, 09:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 19
Default Concatenate unique values among duplicates

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" wrote:

In article ,
Toby wrote:

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?

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



  #6   Report Post  
Old April 14th 09, 10:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 265
Default Concatenate unique values among duplicates

In article ,
Toby wrote:

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$10 0<"",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(I F(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.

--
Domenic
http://www.xl-central.com
  #7   Report Post  
Old April 15th 09, 05:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,180
Default Concatenate unique values among duplicates

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicates and unique values sragor Excel Worksheet Functions 1 February 3rd 09 09:22 AM
Identifying unique values among duplicates bob Excel Worksheet Functions 4 November 10th 08 10:43 PM
Counting Unique Values with Duplicates DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM
How do I sum unique values among duplicates horizontally Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM
check for duplicates, then sum unique values Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM


All times are GMT +1. The time now is 07:03 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017