Find duplicate from similar text

[bcsyeung]

Hi,

I have a long list of uncleaned data of account names with similar names. See below example. 1st col below shows that there are 5 accounts, but after manual adjustment line by line, there are only two accounts namely ABC and Foser.

Is there anyway to use formula to remove duplicates instead of eyeballing them line by line? Appreciate for your help with thanks.​

[TABLE="width: 406"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Original Text[/TD]
[TD="colspan: 2"]Outcome by manual adjustment[/TD]
[/TR]
[TR]
[TD]ABC NEW ENERGY HOLDINGS LIMITED[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC-Poly (Taicang Harbour) Limited[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC-Poly Energy Holdings Limited[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Foser Electric Company (Hong Kong) Limited[/TD]
[TD]Foser[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Foser Group[/TD]
[TD]Foser[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[KolGuyXcel]

On what basis "ABC NEW ENERGY HOLDINGS LIMITED" is getting "adjusted" as "ABC"? Just because it is the first word in the phrase?

Also, could we have more possibilities than "ABC" and "Foser"?

 

[bcsyeung]

Yes if the first or first two words are the same in the phrase then I assume they are the same.

 

[KolGuyXcel]

Try this.

Sub dup_to_unique()
Dim s As String, un_arr() As String, rng As Range, i As Long
s = "ABC,Foser"
un_arr = Split(s, ",")
For Each rng In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = LBound(un_arr) To UBound(un_arr)
If Left(rng.Value2, Len(un_arr(i))) = un_arr(i) Then
rng.Offset(, 1).Value = un_arr(i)
End If
Next
Next
End Sub