Find similar items from a two fields.

[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

 

[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

 

[Rick Rothstein]

Can you describe what you mean by "similar"?

 

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

 

[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

 

[Rick Rothstein]

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

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

 

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

 

[Jacob Skaria]

Nice examples Rick..

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

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

--
Rick (MVP - Excel)




.

 

[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

 

[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

 

[Heera Chavan]

Thank you....Jocob.....your macro is working fine.....

 

[Tim Williams]

Try this out - it has sample VB code at the bottom of the page.

http://www.merriampark.com/ld.htm

Tim

 

[Heera Chavan]

Tim this code was simply great..............thank a ton...............