You can use the following VBA code to remove duplicates from a delimited list. Public Function RemoveDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As Object, tmpOutput As String
Set tmpDict = CreateObject("Scripting.Dictionary")
arrSplit = Split(list, delimiter)
For i = LBound(arrSplit) To UBound(arrSplit)
If Not tmpDict.Exists(arrSplit(i)) Then
tmpDict.Add arrSplit(i), arrSplit(i)
tmpOutput = tmpOutput & arrSplit(i) & delimiter
End If
Next i
If tmpOutput <> "" Then tmpOutput = Left(tmpOutput, Len(tmpOutput) - Len(delimiter))
RemoveDuplicates = tmpOutput
'housekeeping
Set tmpDict = Nothing
End Function
Sub ZapDuplicatesInPlace()
Dim r As Range, va() As Variant
Set r = Application.InputBox("Select range to remove duplicates cell by cell.", "Remove Duplicates From Lists", , , , , , 8)
va = r.Value
For i = LBound(va, 1) To UBound(va, 1)
For j = LBound(va, 2) To UBound(va, 2)
'This assumes delimiter is comma followed by space.
va(i, j) = RemoveDuplicates(CStr(va(i, j)), ", ")
Next j
Next i
'Print output to sheet
r.Value = va
End Sub
There are two ways you can use this code to achieve what you want. If you want to remove the duplicates in place, i.e., if you want to clean the data you have and delete the duplicates forever, you can run the ZapDuplicatesInPlace sub. It will prompt you to select a range that you want it to process. Each cell in the range will be stripped of duplicates. If you
would rather use worksheet functions to leave your original data intact, you can use the function RemoveDuplicates in a formula. For example, if you have Smith, Miller, Patty, Smith, Patty, Miller in A1, you can use the formula below in another cell to return the list minus the duplicates. =RemoveDuplicates(A1,", ")
For instructions for using VBA in your workbook, see this post.
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:
| Book1 |
|---|
|
|
|---|
| A | B | C | D |
|---|
| 1 | Name
| dup_name
| address
| Location
|
|---|
| 2 | abc
| acc
| London
| Kingston 265
|
|---|
| 3 | abc
| acc
| London
| Kingston 985
|
|---|
| 4 | abc
| acc
| Paris
| Croydon 124
|
|---|
| 5 | abc
| acc
| Paris
| Croydon 555
|
|---|
| 6 | xyz
| nnn
| USA
| Chicago 766
|
|---|
| 7 | xyz
| nnn
| USA
| Boston 443
|
|---|
| 8 | xyz
| nnn
| USA
| Boston 442
|
|---|
Sheet1 (2) |
|---|
After:
| Book1 |
|---|
|
|
|---|
| A | B | C | D |
|---|
| 1 | Name
| dup_name
| address
| Location
|
|---|
| 2 | abc
| acc
| London
| Kingston 265,Kingston 985
|
|---|
| 3 | abc
| acc
| Paris
| Croydon 124,Croydon 555
|
|---|
| 4 | xyz
| nnn
| USA
| Chicago 766,Boston 443,Boston 442
|
|---|
| 5 |
|
|
|
|
|---|
| 6 |
|
|
|
|
|---|
| 7 |
|
|
|
|
|---|
| 8 |
|
|
|
|
|---|
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. -
1655172440523.png 41 KB · Views: 15 -
1655172469569.png 44.3 KB · Views: 15
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.
| Book2 |
|---|
|
|
|---|
| A | B | C | D | E | F | G | H | I |
|---|
| 1 | Name
| dup_name
| address
| Location
|
| Name
| dup_name
| address
| Location
|
|---|
| 2 | abc
| acc
| London
| Kingston 265
|
| abc
| acc
| London
| Kingston 265,Kingston 985
|
|---|
| 3 | abc
| acc
| London
| Kingston 985
|
| abc
| acc
| Paris
| Croydon 124,Croydon 555
|
|---|
| 4 | abc
| acc
| Paris
| Croydon 124
|
| xyz
| nnn
| USA
| Chicago 766,Boston 443,Boston 442
|
|---|
| 5 | abc
| acc
| Paris
| Croydon 555
|
|
|
|
|
|
|---|
| 6 | xyz
| nnn
| USA
| Chicago 766
|
|
|
|
|
|
|---|
| 7 | xyz
| nnn
| USA
| Boston 443
|
|
|
|
|
|
|---|
| 8 | xyz
| nnn
| USA
| Boston 442
|
|
|
|
|
|
|---|
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:
| Book1 |
|---|
|
|
|---|
| A | B | C | D |
|---|
| 1 | Name
| dup_name
| address
| Location
|
|---|
| 2 | abc
| acc
| London
| Kingston 265
|
|---|
| 3 | abc
| acc
| London
| Kingston 985
|
|---|
| 4 | abc
| acc
| Paris
| Croydon 124
|
|---|
| 5 | abc
| acc
| Paris
| Croydon 555
|
|---|
| 6 | xyz
| nnn
| USA
| Chicago 766
|
|---|
| 7 | xyz
| nnn
| USA
| Boston 443
|
|---|
| 8 | xyz
| nnn
| USA
| Boston 442
|
|---|
Sheet1 (2) |
|---|
After:
| Book1 |
|---|
|
|
|---|
| A | B | C | D |
|---|
| 1 | Name
| dup_name
| address
| Location
|
|---|
| 2 | abc
| acc
| London
| Kingston 265,Kingston 985
|
|---|
| 3 | abc
| acc
| Paris
| Croydon 124,Croydon 555
|
|---|
| 4 | xyz
| 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.
How do you remove duplicates from a comma separated string?
We can remove duplicates from a string in the following three steps: Convert comma separated string to an array; Use array_unique() to remove duplicates; Convert the array back to a comma separated string.
How do I remove duplicates from a string in Excel?
Remove duplicate values. Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates. ... . Click OK.. What is the easiest way to remove duplicates in Excel?
Open an Excel workbook, or create a new one if you want to follow along. ... . Select a column (or columns) to look for duplicated data. ... . Open the Data tab at the top of the ribbon.. Find the Data Tools menu, and click Remove Duplicates.. Press the OK button on the pop-up to remove duplicate items from your data set.. How do you remove duplicates in CSV?
To remove duplicate rows, find the column that should be unique. Click the column header, and select Remove Duplicates. This will create a new dataset with only one row for each value.
|
