copmapre and rank cells
 

Howdy,

I would like to compare cells that I know are not exact matches. If the
cells have more than 3,4,5,x characters different I want to return a value
basically telling me how close of a match I have.

Any ideas?

thanks,
Mike

copmapre and rank cells
 

Hi Mike,

You could try something like what I have below, however it doesn't deal with
multiple instances of strings within strings.

'The function "Compare" returns zero if Second value doesn't exist in First
value
'or if both values are equal
'Otherwise "Compare" returns the difference between the two strings lengths
'Test by sticking values in Range("A1") and Range("A2") on any worksheet

Function Compare(Rng1, Rng2) As Integer
Dim First As Variant, Second As Variant
If Len(Rng1) = Len(Rng2) Then
First = Rng1
Second = Rng2
Else
First = Rng2
Second = Rng1
End If

If InStr(First, Second) 0 Then
Compare = Len(First) - Len(Second)
Else
Compare = 0
End If
End Function

Sub TestIt()
MsgBox Compare(Range("A1").Value, Range("A2").Value)
End Sub

Hope this helps,
Regards, Rocky McKinley
"MikeD" wrote in message
news:SHb1b.219066$YN5.149586@sccrnsc01...
Howdy,

I would like to compare cells that I know are not exact matches. If the
cells have more than 3,4,5,x characters different I want to return a value
basically telling me how close of a match I have.

Any ideas?

thanks,
Mike

compare and rank cells
 

I'll give that a try. thanks

and I have no idea where 'copmapre' came from. Must have slipped by the
spell checker.



"Rocky McKinley" wrote in message
...
Hi Mike,

You could try something like what I have below, however it doesn't deal
with
multiple instances of strings within strings.

'The function "Compare" returns zero if Second value doesn't exist in
First
value
'or if both values are equal
'Otherwise "Compare" returns the difference between the two strings
lengths
'Test by sticking values in Range("A1") and Range("A2") on any worksheet

Function Compare(Rng1, Rng2) As Integer
Dim First As Variant, Second As Variant
If Len(Rng1) = Len(Rng2) Then
First = Rng1
Second = Rng2
Else
First = Rng2
Second = Rng1
End If

If InStr(First, Second) 0 Then
Compare = Len(First) - Len(Second)
Else
Compare = 0
End If
End Function

Sub TestIt()
MsgBox Compare(Range("A1").Value, Range("A2").Value)
End Sub

Hope this helps,
Regards, Rocky McKinley
"MikeD" wrote in message
news:SHb1b.219066$YN5.149586@sccrnsc01...
Howdy,

I would like to compare cells that I know are not exact matches. If the
cells have more than 3,4,5,x characters different I want to return a
value
basically telling me how close of a match I have.

Any ideas?

thanks,
Mike