| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find similar items from a two fields.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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 --------------- Jacob Skaria "Heera Chavan" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Heera Chavan" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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 Chavan" wrote: 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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Heera Chavan" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 Chavan" wrote: 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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Heera Chavan" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
Thank you....Jocob.....your macro is working fine..... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
Can you describe what you mean by "similar"?
-- Rick (MVP - Excel) "Heera Chavan" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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" wrote: Can you describe what you mean by "similar"? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
So are you saying that these would all be "similar"?
I-11524A I-99524A M-11524A Z-12924A etc. -- Rick (MVP - Excel) "Heera Chavan" wrote in message ... 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" wrote: Can you describe what you mean by "similar"? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
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. "Rick Rothstein" wrote: So are you saying that these would all be "similar"? I-11524A I-99524A M-11524A Z-12924A etc. -- Rick (MVP - Excel) "Heera Chavan" wrote in message ... 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" wrote: Can you describe what you mean by "similar"? . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
Nice examples Rick..
"Rick Rothstein" wrote: So are you saying that these would all be "similar"? I-11524A I-99524A M-11524A Z-12924A etc. -- Rick (MVP - Excel) "Heera Chavan" wrote in message ... 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" wrote: Can you describe what you mean by "similar"? . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
Try this out - it has sample VB code at the bottom of the page. http://www.merriampark.com/ld.htm Tim On Nov 12, 11:07*pm, Heera Chavan wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find similar items from a two fields.
Tim this code was simply great..............thank a ton............... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using the lookup Function for similar items to add together | Excel Worksheet Functions | |||
| using macro, how can i copy similar fields from other xls files? | Excel Discussion (Misc queries) | |||
| Grouping Similar items | Excel Worksheet Functions | |||
| counting similar items in a column | Excel Discussion (Misc queries) | |||
| Counting groups of similar items | Excel Programming | |||
![Heera Chavan[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
