How do i remove duplicates from a comma separated string in excel?

If you are unfamiliar with VBA, try below steps:
1) Alt-F11 to open VBA window
2) Insert/ Modules/Module1
3) paste below code into edit window
4) press F5 to run code, or assign it into a button on worksheet

VBA Code:

Option Explicit Sub dupplicate() Dim lr&, i&, rng, name As String, arr(), key Dim dic As Object Set dic = CreateObject("Scripting.dictionary") lr = Cells(Rows.Count, "A").End(xlUp).Row rng = Range("A2:D" & lr).Value For i = 1 To lr - 1 name = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 3) If Not dic.exists(name) Then dic.Add name, rng(i, 4) Else dic(name) = dic(name) & "," & rng(i, 4) End If Next Range("A2:D" & lr).ClearContents ReDim arr(1 To dic.Count, 1 To 4) i = 0 For Each key In dic.keys i = i + 1 arr(i, 1) = Split(key, "|")(0) arr(i, 2) = Split(key, "|")(1) arr(i, 3) = Split(key, "|")(2) arr(i, 4) = dic(key) Next Range("A2").Resize(i, 4).Value = arr End Sub

Before:

Sheet1 (2)

After:

Sheet1

Try this is you want to use formula:

- First: use =UNIQUE(A2:C8) to take data for column Names/Dub names & address
- Second: use =TEXTJOIN(", ",1,IF(($A$2:$A$8=A12)*($B$2:$B$8=B12)*($C$2:$C$8=C12),$D$2:$D$8,"")) for location.

Try this is you want to use formula:

**Try this IF you want to use formula:

Another option is Power Query: convert your source table to an official table (click in table and hit Ctrl-t and indicate that you have headers). Then while in the table, click Data > From Table/Range to open Power Query. Then the easiest approach would be to open the Advanced Editor by clicking View > Advanced Editor and delete all of the code except for the first two lines (leaving let and the Source line...then paste in everything below the Source line in this M-code:

Power Query:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Name", "dup_name", "address"}, {{"Location", each Text.Combine([Location], ","), type text}}) in #"Grouped Rows"

Then click Done to close out the editor. Then to load the transformed table to your worksheet, click Home > Close & Load To and indicate whether you want to load the table to a new worksheet, on the current one, etc.

Sheet1

If you are unfamiliar with VBA, try below steps:
1) Alt-F11 to open VBA window
2) Insert/ Modules/Module1
3) paste below code into edit window
4) press F5 to run code, or assign it into a button on worksheet

VBA Code:

Option Explicit Sub dupplicate() Dim lr&, i&, rng, name As String, arr(), key Dim dic As Object Set dic = CreateObject("Scripting.dictionary") lr = Cells(Rows.Count, "A").End(xlUp).Row rng = Range("A2:D" & lr).Value For i = 1 To lr - 1 name = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 3) If Not dic.exists(name) Then dic.Add name, rng(i, 4) Else dic(name) = dic(name) & "," & rng(i, 4) End If Next Range("A2:D" & lr).ClearContents ReDim arr(1 To dic.Count, 1 To 4) i = 0 For Each key In dic.keys i = i + 1 arr(i, 1) = Split(key, "|")(0) arr(i, 2) = Split(key, "|")(1) arr(i, 3) = Split(key, "|")(2) arr(i, 4) = dic(key) Next Range("A2").Resize(i, 4).Value = arr End Sub

Before:

Book1ABCD1Name dup_name address Location 2abc acc London Kingston 265 3abc acc London Kingston 985 4abc acc Paris Croydon 124 5abc acc Paris Croydon 555 6xyz nnn USA Chicago 766 7xyz nnn USA Boston 443 8xyz nnn USA Boston 442

Sheet1 (2)

After:

Book1ABCD1Name dup_name address Location 2abc acc London Kingston 265,Kingston 985 3abc acc Paris Croydon 124,Croydon 555 4xyz nnn USA Chicago 766,Boston 443,Boston 442 5 6 7 8

Sheet1

Thanks allot and this is what I was expecting.. My bad that I didn't ask/mention for VB code in my question since this is to append in one of my codes.. Thanks again.

Try this is you want to use formula:

- First: use =UNIQUE(A2:C8) to take data for column Names/Dub names & address
- Second: use =TEXTJOIN(", ",1,IF(($A$2:$A$8=A12)*($B$2:$B$8=B12)*($C$2:$C$8=C12),$D$2:$D$8,"")) for location.

I like this formula aswell but at this moment looking for vb code. Appreciate your help.