Merging Data

[Saxman]

I have a copy of ActiveData installed for use within Excel 2010.

Not an ActiveData problem, but I need to match horseracing jockeys names.

One database lists it as e.g. Aidan Coleman and the other database
Coleman, A making it impossible for ActiveData to do the matching.

I need a bit of code or similar so that Aiden Coleman becomes Coleman, A

In other words I need the christian name and surname (or inital) to be
reversed.

Here is an example.

Jason Maguire
Leighton Aspell
Daryl Jacob
Paddy Brennan
Richard Johnson
Brian Hughes
Sam Twiston-Davies

They need to be:-

Maguire Jason
Aspell Leighton
Jacob Daryl
Brennan Paddy
Johnson Richard
Hughes Brian
Twiston-Davies Sam

 

[Claus Busch]

Hi,

Am Sat, 11 May 2013 19:14:12 +0100 schrieb Saxman:

Jason Maguire
Leighton Aspell
Daryl Jacob
Paddy Brennan
Richard Johnson
Brian Hughes
Sam Twiston-Davies

They need to be:-

Maguire Jason
Aspell Leighton
Jacob Daryl
Brennan Paddy
Johnson Richard
Hughes Brian
Twiston-Davies Sam

your names in column A. Then try:
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Sat, 11 May 2013 20:22:01 +0200 schrieb Claus Busch:

your names in column A. Then try:
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

if you want the name with comma and first name initial try:
=MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,1)


Regards
Claus Busch

 

[Saxman]

=MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,1)

That works almost perfectly except that

Mr C P Shoemark is returned as C P Shoemark, M,
so ActiveData would miss it.

The option above is preferable because some jockeys have their christian
names listed and others an initial letter.

 

[Claus Busch]

Hi,

Am Sat, 11 May 2013 19:39:40 +0100 schrieb Saxman:

That works almost perfectly except that

Mr C P Shoemark is returned as C P Shoemark, M,
so ActiveData would miss it.

then try:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",))>1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)&", "&SUBSTITUTE(LEFT(A1,LEN(MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+2,99))),"Mr ",),MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,1))


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Sat, 11 May 2013 21:00:34 +0200 schrieb Claus Busch:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",))>1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)&", "&SUBSTITUTE(LEFT(A1,LEN(MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+2,99))),"Mr ",),MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,1))

or try it with a macro:

Sub Strings()
Dim LRow As Long
Dim rngC As Range
Dim myName As String
Dim myFirstN As String
Dim myInit As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
myName = Right(rngC, Len(rngC) - InStrRev(rngC, " "))
myFirstN = Trim(Replace(Replace(rngC, myName, ""), "Mr ", ""))
myInit = Left(rngC, 1)
If Len(rngC) - Len(Replace(rngC, " ", "")) > 1 Then
rngC.Offset(0, 1) = myName & ", " & myFirstN
Else
rngC.Offset(0, 1) = myName & ", " & myInit
End If
Next
End Sub

Regards
Claus Busch

 

[Saxman]

Hi,

Am Sat, 11 May 2013 21:00:34 +0200 schrieb Claus Busch:


or try it with a macro:

Sub Strings()
Dim LRow As Long
Dim rngC As Range
Dim myName As String
Dim myFirstN As String
Dim myInit As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
myName = Right(rngC, Len(rngC) - InStrRev(rngC, " "))
myFirstN = Trim(Replace(Replace(rngC, myName, ""), "Mr ", ""))
myInit = Left(rngC, 1)
If Len(rngC) - Len(Replace(rngC, " ", "")) > 1 Then
rngC.Offset(0, 1) = myName & ", " & myFirstN
Else
rngC.Offset(0, 1) = myName & ", " & myInit
End If
Next
End Sub

Regards
Claus Busch

Thank you Ron & Claus. I will have a look at this on Sunday and report
back.

 

[Saxman]

Hi,

Am Sat, 11 May 2013 21:00:34 +0200 schrieb Claus Busch:


or try it with a macro:

Sub Strings()
Dim LRow As Long
Dim rngC As Range
Dim myName As String
Dim myFirstN As String
Dim myInit As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
myName = Right(rngC, Len(rngC) - InStrRev(rngC, " "))
myFirstN = Trim(Replace(Replace(rngC, myName, ""), "Mr ", ""))
myInit = Left(rngC, 1)
If Len(rngC) - Len(Replace(rngC, " ", "")) > 1 Then
rngC.Offset(0, 1) = myName & ", " & myFirstN
Else
rngC.Offset(0, 1) = myName & ", " & myInit
End If
Next
End Sub

Regards
Claus Busch

I get a #REF! error with the first and these names for example.
Jason Maguire
Leighton Aspell
Daryl Jacob
Paddy Brennan
Richard Johnson
Brian Hughes
Brendan Powell
Tony Kelly
Sam Twiston-Davies
Denis O'Regan
Paul Moloney
Ryan Mania
Jamie Moore
Richie McLernon
Wayne Hutchinson
James Banks
Peter Buchanan


The above code works fine and gives me the following results.

Jason Maguire Maguire, J
Leighton Aspell Aspell, L
Daryl Jacob Jacob, D
Paddy Brennan Brennan, P
Richard Johnson Johnson, R
Brian Hughes Hughes, B
Brendan Powell Powell, B
Tony Kelly Kelly, T
Sam Twiston-Davies Twiston-Davies, S
Denis O'Regan O'Regan, D
Paul Moloney Moloney, P
Ryan Mania Mania, R
Jamie Moore Moore, J
Richie McLernon McLernon, R
Wayne Hutchinson Hutchinson, W
James Banks Banks, J
Peter Buchanan Buchanan, P
Ian Popham Popham, I

Thank you very much. Much appreciated. It will enable me to record the
jockeys strike rate quite easily.

 

[Saxman]

It occurs to me the regex should be case insensitive, otherwise it will cause MR. and Mr. to be different. So I would add the .IgnoreCase line as below.

=====================
Function RevName(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
'add to .Pattern, in a pipe-delimited fashion as below, any other TITLES to be excluded
.Pattern = "^(?:Miss|Mrs|Mr|M|Dr)?\W*(.*)(?=\s+\b\S+\b)\s+(\S+)"
.Global = True
.ignorecase = True
RevName = .Replace(s, "$2 $1")
End With
End Function
============================

That works fine Ron except if a jockey has a christian name of Mickey,
it knocks off the 'M'. (See below). It's the surname that I'm more
interested in.

Martin Harley Harley artin
Martin Lane Lane artin
Michael J M Murphy Murphy ichael J M
Matthew Davies Davies atthew