Merging Data

S

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
 
C

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
Click to expand...

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


Regards
Claus Busch
 
C

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))
Click to expand...

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


Regards
Claus Busch
 
S

Saxman

=MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,1)
Click to expand...

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

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.
Click to expand...

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
 
C

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))
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

Here is a UDF that will return the Last Name, followed by a <space> followed by everything else. However, if there are Titles present (e.g. Mr. Mrs, etc) it will exclude them.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.Option Explicit

==========================================
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
RevName = .Replace(s, "$2 $1")
End With
End Function
==============================

To use this User Defined Function (UDF), enter a formula like

=RevName(A1)

in some cell.
 
S

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
Click to expand...

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

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
Click to expand...

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

Ron Rosenfeld

I will try this later, thanks.
Click to expand...

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
============================
 
S

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
============================
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

Sorry about that. I overlooked Names starting with "M". Easy fix though with a minor change in the regex pattern:

..pattern = "^(?:(?:Mrs|Mr|M|Dr)\b)?\W*(.*)(?=\s+\b\S+\b)\s+(\S+)"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenating fields 1

Top