Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Rank a column but not include some cells | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Can I assign a rank based on a cells' value | Excel Worksheet Functions |