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 |
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 |
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 |
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 |
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 |
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 |
Concatenate unique values among duplicates
|
All times are GMT +1. The time now is 07:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com