Removing Duplicates in a single cell with multiple separator

[JezaDominic]

Hi I'm having a helluva time with excel since I remove duplicates from 33k row of entries from column A to P
Now I came across an issue, I need to merge multiple address in a single file row how ever each entry have multiple name, I need help in removing those multiple names after merging.
Below is an example, Each row has a merged duplicate entry per cell, the duplicated need to be remove but after running macros it seems that it does not work per cell.

[TABLE="width: 2202"]
<tbody>[TR]
[TD]1825
12664[/TD]
[TD]1186 Holly Ave
3415 Beech St[/TD]
[TD][/TD]
[TD]Imperial Beach
San Diego[/TD]
[TD][/TD]
[TD]United States Of America
United States Of America[/TD]
[TD][/TD]
[TD]1220 Pacific Hwy #241
1220 Pacific Hwy #241[/TD]
[TD][/TD]
[TD]San Diego
San Diego[/TD]
[TD]CA
CA[/TD]
[TD]92101
92101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]11777
26250[/TD]
[TD]321-27 Imperial Ave
5321 Imperial Ave[/TD]
[TD][/TD]
[TD]San Diego
San Diego[/TD]
[TD][/TD]
[TD]United House Of Prayer For
United House Of Prayer For[/TD]
[TD][/TD]
[TD]3200 Freedom Dr
3200 Freedom Dr[/TD]
[TD][/TD]
[TD]Charlotte
Charlotte[/TD]
[TD]NC
NC[/TD]
[TD]28208
28208[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]28580
3043
17622[/TD]
[TD]6707 Amherst St
1328 Hoover Ave
41 E 6th St[/TD]
[TD][/TD]
[TD]San Diego
National City
National City[/TD]
[TD][/TD]
[TD]United Brokers Realty Inc
United Brokers Realty Inc
United Brokers Realty Inc[/TD]
[TD]Cooper Scafani & Assocs[/TD]
[TD]5155 Waring Rd
5155 Waring Rd
5155 Waring Rd[/TD]
[TD][/TD]
[TD]San Diego
San Diego
San Diego[/TD]
[TD]CA
CA
CA[/TD]
[TD]92120
92120
92120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]8328
12180[/TD]
[TD]2330 Grove Ave
330-56 Grove Ave[/TD]
[TD][/TD]
[TD]San Diego
San Diego[/TD]
[TD][/TD]
[TD]Unison Investments
Unison Investments[/TD]
[TD]Jen-Liang Wu - Ptr.
Jen-Liang Wu - Ptr.[/TD]
[TD]23545 Crenshaw Bl #201
23545 Crenshaw Bl #201[/TD]
[TD][/TD]
[TD]Torrance
Torrance[/TD]
[TD]CA
CA[/TD]
[TD]90505
90505[/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[Paulo L]

It's hard to visualize what you are trying to do. Can you create a simple example of before and after, showing what you are trying to achieve?

 

[JezaDominic]

Thanks you for your quick responce here is an example of the after, using the example above.


[TABLE="width: 1062"]
<colgroup><col><col><col span="2"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Address 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Owner.Name[/TD]
[TD]Owner.Company[/TD]
[TD]Owner.Address 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1825[/TD]
[TD]1186 Holly Ave[/TD]
[TD]Imperial Beach[/TD]
[TD] [/TD]
[TD]United States Of America[/TD]
[TD] [/TD]
[TD]1220 Pacific Hwy #241[/TD]
[TD] [/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD="align: right"]92101[/TD]
[/TR]
[TR]
[TD="align: right"]12664[/TD]
[TD]3415 Beech St[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11777[/TD]
[TD]321-27 Imperial Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]United House Of Prayer For[/TD]
[TD] [/TD]
[TD]3200 Freedom Dr[/TD]
[TD] [/TD]
[TD]Charlotte[/TD]
[TD]NC[/TD]
[TD="align: right"]28208[/TD]
[/TR]
[TR]
[TD="align: right"]26250[/TD]
[TD]5321 Imperial Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]28580[/TD]
[TD]6707 Amherst St[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]United Brokers Realty Inc[/TD]
[TD]Cooper Scafani & Assocs[/TD]
[TD]5155 Waring Rd[/TD]
[TD] [/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD="align: right"]92120[/TD]
[/TR]
[TR]
[TD="align: right"]3043[/TD]
[TD]1328 Hoover Ave[/TD]
[TD]National City[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]17622[/TD]
[TD]41 E 6th St[/TD]
[TD]National City[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8328[/TD]
[TD]2330 Grove Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]Unison Investments[/TD]
[TD]Jen-Liang Wu - Ptr.[/TD]
[TD]23545 Crenshaw Bl #201[/TD]
[TD] [/TD]
[TD]Torrance[/TD]
[TD]CA[/TD]
[TD="align: right"]90505[/TD]
[/TR]
[TR]
[TD="align: right"]12180[/TD]
[TD]330-56 Grove Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I really would appreciate your help.. TIA

 

[JezaDominic]

Paulo L thanks your quick reponse

Below is what I'm trying to achieve.
[TABLE="width: 1126"]
<colgroup><col><col><col span="2"><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Address 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Owner.Name[/TD]
[TD]Owner.Company[/TD]
[TD]Owner.Address 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1825[/TD]
[TD]1186 Holly Ave[/TD]
[TD]Imperial Beach[/TD]
[TD] [/TD]
[TD]United States Of America[/TD]
[TD] [/TD]
[TD]1220 Pacific Hwy #241[/TD]
[TD] [/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD="align: right"]92101[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12664[/TD]
[TD]3415 Beech St[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11777[/TD]
[TD]321-27 Imperial Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]United House Of Prayer For[/TD]
[TD] [/TD]
[TD]3200 Freedom Dr[/TD]
[TD] [/TD]
[TD]Charlotte[/TD]
[TD]NC[/TD]
[TD="align: right"]28208[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]26250[/TD]
[TD]5321 Imperial Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]28580[/TD]
[TD]6707 Amherst St[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]United Brokers Realty Inc[/TD]
[TD]Cooper Scafani & Assocs[/TD]
[TD]5155 Waring Rd[/TD]
[TD] [/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD="align: right"]92120[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3043[/TD]
[TD]1328 Hoover Ave[/TD]
[TD]National City[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]17622[/TD]
[TD]41 E 6th St[/TD]
[TD]National City[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8328[/TD]
[TD]2330 Grove Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD]Unison Investments[/TD]
[TD]Jen-Liang Wu - Ptr.[/TD]
[TD]23545 Crenshaw Bl #201[/TD]
[TD] [/TD]
[TD]Torrance[/TD]
[TD]CA[/TD]
[TD="align: right"]90505[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12180[/TD]
[TD]330-56 Grove Ave[/TD]
[TD]San Diego[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
I'd really appreciate your help on this.

 

[JezaDominic]

I tried running this but Its prompting me out of range
Sub Remove_DupesInString()
Dim str As String
Dim fval As String
Dim strArr() As String
Dim x As Long
Dim k As Long
Set sht = Sheets("Sheet1")
Lastrow = sht.Cells(Cells.Rows.Count, "P99999").End(xlUp).Row
For Each cell In sht.Range("$B$2:P" & Lastrow)
Erase strArr
fval = ""
str = cell.Value
strArr = Split(str, "/")
For rw = 0 To UBound(strArr)
For k = rw + 1 To UBound(strArr)
If Trim(strArr(k)) = Trim(strArr(rw)) Then
strArr(k) = "" 'if duplicate clear array value
End If
Next k
Next rw
For x = 0 To UBound(strArr)
If strArr(x) <> "" Then
fval = fval & Trim(strArr(x)) & "/"
End If
Next x
fval = Left(fval, Len(fval) - 1)
cell.Value = fval
Next cell
End Sub