Need a formula or Query to list out unique values

[signup]

I have a sheet similar to the below data. Can we list out the unique names for each location? as shown in the below "desired output" section?

Location	Names
Location 1	Name 1, Name 2, Name 4
Location 1	Name 1, Name 4
Location 1	Name 2, Name 3, Name 5
Location 1	Name 1
Location 1	Name 2
Location 1	Name 6
Location 2	Name 7, Name 1, Name 8
Location 2	Name 2, Name 1
Location 2	Name 2
Location 2	Name 2, Name 9
Location 2	Name 10
Location 3	Name 11, Name 12, Name 1
Location 3	Name 13
Location 3	Name 1
Location 3	Name 11
Location 3	Name 1, Name 14

Desired Output:

Location	Names
Location 1	Name 1, Name 2, Name 3, Name 4, Name 5, Name 6
Location 2	Name 1, Name 2, Name 7, Name 8, Name 9, Name 10
Location 3	Name 1, Name 11, Name 12, Name 13, Name 14

Thanks in advance!

 

[Fluff]

In that case check that you don't have any leading/trailing spaces in the Location cells. Also the code is case sensitive, but that can be changed very easily if needed.

 

[alansidman]

Have you tried to replicate the Power Query Solution. Further info on this Excel function if you are not aware.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[signup]

An alternative solution is to use Power Query to find the unique items. Mcode follows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Then bring the query into power pivot and create a new measure


and then the final result

Book5
	A	B
1	Column1	New Value
2	Location 1	Name 1, Name 2, Name 4, Name 3, Name 5, Name 6
3	Location 2	Name 7, Name 1, Name 8, Name 2, Name 9, Name 10
4	Location 3	Name 11, Name 12, Name 1, Name 13, Name 14
Table1

First of all my sincere apologies for the late reply. Due to personal commitments, I couldn't reply back early. @Fluff @alansidman

Thanks for providing the power query, I'm new to use these things like Power Query and Macros. Whenever I feel the required output can be done in another way(By saving the hours of manual time) then I'm requesting in the forum. I just know how to apply the power query on a table of data. So, I'm unable to do the second step(create a new measure) u have mentioned in the post.

Can you please help me with that? Thanks once again for taking the time and providing the help.

 

[signup]

No VBA. Three steps, the best one was modified from stuff I found on stackexchange. textjoin based on location; the stakeexchange part turns that long string into a new array, then unique the array and join it back together.
:

MrExcelPlayground.xlsm
	L	M	N	O	P	Q	R
3	Location	Names
4	Location 1	Name 1, Name 2, Name 4
5	Location 1	Name 1, Name 4
6	Location 1	Name 2, Name 3, Name 5
7	Location 1	Name 1
8	Location 1	Name 2		Location	Concatenated	uniques	All in one formula
9	Location 1	Name 6		Location 1	Name 1, Name 2, Name 4, Name 1, Name 4, Name 2, Name 3, Name 5, Name 1, Name 2, Name 6	Name 1, Name 2, Name 4, Name 3, Name 5, Name 6	Name 1, Name 2, Name 4, Name 3, Name 5, Name 6
10	Location 2	Name 7, Name 1, Name 8		Location 2	Name 7, Name 1, Name 8, Name 2, Name 1, Name 2, Name 2, Name 9, Name 10	Name 7, Name 1, Name 8, Name 2, Name 9, Name 10	Name 7, Name 1, Name 8, Name 2, Name 9, Name 10
11	Location 2	Name 2, Name 1		Location 3	Name 11, Name 12, Name 1, Name 13, Name 1, Name 11, Name 1, Name 14	Name 11, Name 12, Name 1, Name 13, Name 14	Name 11, Name 12, Name 1, Name 13, Name 14
12	Location 2	Name 2
13	Location 2	Name 2, Name 9
14	Location 2	Name 10
15	Location 3	Name 11, Name 12, Name 1
16	Location 3	Name 13
17	Location 3	Name 1
18	Location 3	Name 11
19	Location 3	Name 1, Name 14
Sheet22

Cell Formulas
Range	Formula
P9:P11	P9	=TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,""))
Q9:Q11	Q9	=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(P9,", ",REPT(" ",99)),IF(SEQUENCE(LEN(P9)-LEN(SUBSTITUTE(P9,", ",""))+1)=1,1,(SEQUENCE(LEN(P9)-LEN(SUBSTITUTE(P9,", ",""))+1)-1)*99),99))))
R9:R11	R9	=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,"")),", ",REPT(" ",99)),IF(SEQUENCE(LEN(TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,"")))-LEN(SUBSTITUTE(TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,"")),", ",""))+1)=1,1,(SEQUENCE(LEN(TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,"")))-LEN(SUBSTITUTE(TEXTJOIN(", ",TRUE,IF($L$4:$L$19=O9,$M$4:$M$19,"")),", ",""))+1)-1)*99),99))))

The "R" column has it all in one nasty formula.

Hey, My sincere apologies for the late reply.

I'm a newbie so it looks a bit confusing to use this on my sheet. Earlier one or two times I have used Power query. So, I would like to have a solution on the same. Thanks once again for taking the time and providing the solution.

 

[alansidman]

Tutorial on how to build the measure in Power Pivot

IMA Site Maintenance