Find similar items from a two fields.

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
 
J

Jacob Skaria

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
 
H

Heera Chavan

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

Heera Chavan

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
 
R

Rick Rothstein

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

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

Heera Chavan

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

Jacob Skaria

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)




.
 
J

Jacob Skaria

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
 
J

Jacob Skaria

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
 

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

Top