Find similar items from a two fields.

  • Thread starter Thread starter Heera Chavan
  • Start date Start date
H

Heera Chavan

Hi,

I am really in a tight spot now.

I need macro which will compare two strings if they are similar.

For example:
StringA = I-11524A
StringB = I-11525B
StringC = I-11525

Now these three string are similar and need a utility which will compare
these strings and highlight them if they are similar.
I tried two three codes like StrComp(str1, str2, vbTextCompare) but it did
not work.

Kindly help.

Regards
Heera Chavan
 
You can check for the first n characters (based on the sepcification and your
requirement) using either LEFT() or using LIKE()...

Dim str1 As String
Dim str2 As String

str1 = "I-11524A"
str2 = "I-11525B"

If str1 Like Left(str2, 6) & "*" Then
MsgBox "Similar"
End If


If this post helps click Yes
 
I need to write a long script for this...any way I am writing a script with
the help of worksheet function SEARCH which is quite similar to your
idea......

Thank you for your support.
 
Hi Rick,

I want to compare two invoice numbers which are same but one or two
character here and there. The diffrence might be because of one "-" or an "*"
is extra in one of the strings or else one number is missing from the left or
the right side of the string.........

I want a robust macro which will compare all this things.....

If you alreday have some thing please, please let me know......

Regards
Heera Chavan
 
So are you saying that these would all be "similar"?

I-11524A
I-99524A
M-11524A
Z-12924A
etc.
 
No here is an example.....

I-11524A would be similar with I11524A, I -11524B, I-111524A I- 11524A
B-115253 would be similar with B115253, B1115253, B115253-A, B-115253B

Like this.........thank you for the response.
 
Nice examples Rick..

Rick Rothstein said:
So are you saying that these would all be "similar"?

I-11524A
I-99524A
M-11524A
Z-12924A
etc.

--
Rick (MVP - Excel)




.
Click to expand...
 
Try the below....which will compare the numeric pieces alone..

Sub Macro()

Dim str1 As String
Dim str2 As String

str1 = "I-11524A"
str2 = "I11524B"

If GetNumericString(str1) = GetNumericString(str2) Then
MsgBox "Similar"
End If

End Sub

Function GetNumericString(strData As String) As String
For inttemp = 1 To Len(strData)
If IsNumeric(Mid(strData, inttemp, 1)) Then
GetNumericString = GetNumericString & Mid(strData, inttemp, 1)
End If
Next
End Function

If this post helps click Yes
 
Heera, you can further customize this to check for the first n numerics; but
that is to be finalized based on your requirement...Analyse more samples and
you can addup more validations to that..

'to compare the 1st 4 numerics

Left(GetNumericString(str1),4) = Left(GetNumericString(str2),4)

If this post helps click Yes
 
Back
Top