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

Original Text	Outcome by manual adjustment
ABC NEW ENERGY HOLDINGS LIMITED	ABC
ABC-Poly (Taicang Harbour) Limited	ABC
ABC-Poly Energy Holdings Limited	ABC
Foser Electric Company (Hong Kong) Limited	Foser
Foser Group	Foser

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[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