Excel-Help-Ideas

[mak]

Hi,
I am downloading some statistics about Jockeys & Trainers from Massey site
which I want to trigger if they match with the Jockey names that BA gives

1. The 1st problem & probably easier to workaround is when I have to add a full stop in some names

BA= A. P. McCoy
Massey= A P McCoy

In order to match it with vlookup I am placing manual in Massey names a full stop & it's ok. Is there a formula that could help me with this?

2. In some selections the names are like following

BA = Robert Thornton
Massey = R Thornton

I can't figure out how I can match them...

Any suggestions?

[Ian]

Problem 2.

I've used the following formula to edit golfers' names.

=LEFT(A5,1)&" "&RIGHT(A5,LEN(A5)-FIND(" ",A5))

Obviously A5 will need changing to whatever column the jockey's name comes in your spreadsheet.

[mak]

Ian thanks
now I have to deal with 1

[osknows]

=SUBSTITUTE(SUBSTITUTE(B7," ",""),".","")

if B7 is

A. P. McCoy = APMcCoy

A P McCoy = APMcCoy

[osknows]

Also found this which may help. If you have 2 cells, say A1 and A2

=Fuzzy(A1,A2) returns the % match between the 2 cells. Testing on your examples above gave 100%

Copy this into a module

Code: Select all

Dim TopMatch         As Integer
Dim strCompare       As String
Function Fuzzy(strIn1 As String, strIn2 As String) As Single
 Dim L1 As Integer
Dim In1Mask(1 To 24) As Long 'strIn1 is 24 characters max
Dim iCh As Integer
Dim N As Long
Dim strTry As String
Dim strTest As String

TopMatch = 0
L1 = Len(strIn1)
strTest = UCase(strIn1)
strCompare = UCase(strIn2)

For iCh = 1 To L1
 In1Mask(iCh) = 2 ^ iCh
 Next iCh

'Loop thru all ordered combinations of characters in strIn1
For N = 2 ^ (L1 + 1) - 1 To 1 Step -1
 strTry = ""
For iCh = 1 To L1
 If In1Mask(iCh) And N Then
 strTry = strTry & Mid(strTest, iCh, 1)
 End If
 Next iCh
If Len(strTry) > TopMatch Then TestString strTry
 Next N

Fuzzy = TopMatch / CSng(L1)
End Function
Sub TestString(strIn As String)
 
 Dim L As Integer
Dim strTry As String
Dim iCh As Integer

L = Len(strIn)
If L <= TopMatch Then Exit Sub

strTry = "*"

For iCh = 1 To L
strTry = strTry & Mid(strIn, iCh, 1) & "*"
Next iCh

If strCompare Like strTry Then
If L > TopMatch Then TopMatch = L
End If

End Sub


[mak]

Thanks Os
I tested the formula & worked fine of course & definitely I will try the module

I am ready now to go & lose some money

Thanks again both

*really super forum

[mak]

Hi again (little more help needed)

I have manage to get the weight figures (Age/Weight from AF column) in one cell

something like

10-12
8-10
10-9

How can I sort them to find the top 6 Most Weighted?

[osknows]

Assume
A1 is 10-12
A2 is 8-10
A3 is 10-9

C1=LEFT(A1,FIND("-",A1,1)-1)*14+RIGHT(A1,LEN(A1)-FIND("-",A1,1))
C2=LEFT(A2,FIND("-",A2,1)-1)*14+RIGHT(A2,LEN(A2)-FIND("-",A2,1))
C3=LEFT(A3,FIND("-",A3,1)-1)*14+RIGHT(A3,LEN(A3)-FIND("-",A3,1))

This converts everything to pounds first. Then in cells

D1=RANK(C1,$C$1:$C$3,1)
D2=RANK(C2,$C$1:$C$3,1)
D3=RANK(C3,$C$1:$C$3,1)

Will rank them highest to lowest

[mak]

Thanks Os again & again.....