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]

Would a macro be ok?

 

[signup]

Would a macro be ok?

Anything which could fulfill the requirement.

 

[Fluff]

Ok, how about

Sub signup()
   Dim Ary As Variant, Sp As Variant
   Dim r As Long, i As Long
   
   With Sheets("Master")
      Ary = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Ary(r, 2)
         Else
            Sp = Split(Ary(r, 2), ",")
            For i = 0 To UBound(Sp)
               If InStr(1, .Item(Ary(r, 1)), Trim(Sp(i)), 1) = 0 Then
                  .Item(Ary(r, 1)) = .Item(Ary(r, 1)) & ", " & Sp(i)
               End If
            Next i
         End If
      Next r
      Sheets("Sheet2").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

Change sheet names to suit.

 

[alansidman]

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

 

[JamesCanale]

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.

 

[signup]

Ok, how about

Sub signup()
   Dim Ary As Variant, Sp As Variant
   Dim r As Long, i As Long
 
   With Sheets("Master")
      Ary = .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Ary(r, 2)
         Else
            Sp = Split(Ary(r, 2), ",")
            For i = 0 To UBound(Sp)
               If InStr(1, .Item(Ary(r, 1)), Trim(Sp(i)), 1) = 0 Then
                  .Item(Ary(r, 1)) = .Item(Ary(r, 1)) & ", " & Sp(i)
               End If
            Next i
         End If
      Next r
      Sheets("Sheet2").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

Change sheet names to suit.

Thank you for taking the time and sharing this code. This seems working but the original values are again repeating after the output.
I'm getting the output like below :

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
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

Can we get the output in the new sheet? so that it would be clear enough to check as I have 400+ rows.

 

[Fluff]

@JamesCanale
The OP is using 2019 & therefore doesn't have the dynamic array functions you've used.

 

[Fluff]

Are you outputting the results to a new sheet as I did, or are you putting them on the same sheet as the data?

 

[signup]

Are you outputting the results to a new sheet as I did, or are you putting them on the same sheet as the data?

Sorry for the late reply. Yes I'm getting output to new sheet.