Standardize names using formula or VBA Code (Help required)

[Sandesh26]

I am in big trouble here I want to Standardize names using macro or any excel function<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Standardize means in a row name is written in different fashion e.g. <o></o>
sandesh Chavan
mr rajesh
mr sandesh
chavan sandesh
Nilesh
m\r nilesh
saurabh
mr saurabh
akshay
Akshay Vaishnav
mr Mahesh
Mahesh (like wise there is thousands of data)
--------<o></o>
Now what I want is from the above the macro should pick few names & rename as below<o></o>
Sandesh Chavan
Nilesh Chavan
Akshay Vaishnav<o></o>
------------
How I am doing now is I put filter on names list use the filter as contains & I put *sandesh* keyword<o></o>
Whatever data papers, I rename those as Sandesh Chavan in new column.<o></o>
Appreciate your help<o></o>
Thanks
Sandesh Chavan
If any of the part is unclear please let me know i am happy to explain again<o></o>

 

[Sandesh26]

link explain above

https://picasaweb.google.com/lh/photo/87fHKl8TXOIpPCX71pQVng?feat=directlink

 

[Sandesh26]

gues any one can help!!!!!

 

[dave3009]

Based on the screenshot

Sub DB_Standardise()
Dim findItems As Long: findItems = Range("A" & Rows.Count).End(xlUp).Row
Dim foundItems As Long: foundItems = Range("E" & Rows.Count).End(xlUp).Row
Dim fndWhat As String
Dim repWhat As String
Dim foundWhat As Range
Dim i As Long, j As Long
For i = 1 To findItems
    fndWhat = Range("A" & i).Value
    repWhat = Range("B" & i).Value
        For j = 1 To foundItems
            If LCase(Range("E" & j)) Like "*" & fndWhat & "*" Then
                Range("E" & j).Offset(, 1) = repWhat
            End If
        Next j
Next i
Range("F1:F" & foundItems).SpecialCells(xlCellTypeBlanks) = "Not Required"

End Sub

 

[Sandesh26]

I use office 2003

Excellent!!!!! Dave

I owe you!!!!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Sandesh26]

Dear Dave 3009,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks a million for these codes but it won’t work in all the cases. eg. Below<o></o>
<o></o>
Name in a column is "Emeraude International" & the keyword is *Emeraude* like wise "Adm International Sarl" & keyword is *Adm* <o></o>
<o></o>
I have slightly modified the code as per my requirement.<o></o>
<o> </o>
Adjustment is names to sanitised are kept in Tab one (namely: DataSheet) & The keywords to match are kept in Tab 2 (Namely : Keywords)<o></o>
<o> </o>
Can please let me know if am doing anything wrong<o></o>
<o> </o>
<o> </o>
Sub DB_Standardise()<o></o>
<o> </o>
Dim findItems As Long: findItems = Sheets("Keywords").Range("A" & Rows.Count).End(xlUp).Row<o></o>
Dim foundItems As Long: foundItems = Sheets("DataSheet").Range("A" & Rows.Count).End(xlUp).Row<o></o>
Dim fndWhat As String<o></o>
Dim repWhat As String<o></o>
Dim foundWhat As Range<o></o>
Dim i As Long, j As Long<o></o>
For i = 1 To findItems<o></o>
fndWhat = Sheets("Keywords").Range("A" & i).Value<o></o>
repWhat = Sheets("Keywords").Range("B" & i).Value<o></o>
For j = 1 To foundItems<o></o>
If LCase(Range("A" & j)) Like "*" & fndWhat & "*" Then<o></o>
Range("A" & j).Offset(, 1) = repWhat<o></o>
End If<o></o>
Next j<o></o>
Next i<o></o>
Range("B1:B" & foundItems).SpecialCells(xlCellTypeBlanks) = ""<o></o>
<o> </o>
Range("B1") = "Regularize name"<o></o>
<o> </o>
End Sub<o></o>

 

[Sandesh26]

Hi All Any luck on this........Thank you!!!