Compare data strings?

[Ed]

I have a series of data strings I need to compare. I am looking for the one
string that has a character found in no other string. What is the best way
to approach this?

Ed

 

[Perry]

Here are a couple of URL demonstrating some functions you can use.
Kindly repost if you get stuck including some lines code.

Instr()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctinstr.asp

InstrRev() does the same as above function except the other way around
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctinstrrev.asp

StrComp()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctstrcompx.asp

 

[Ed]

Thanks for the links, Perry. I was considering using InStr, but I think I'm
looking at a very Neanderthal approach, and was wondering if there was a
better way. I have several strings (about a dozen) of varying length in a
one-dimensional array. One of these strings MAY contain one single
character that is not found in any of the other strings.

The only approach I can come up with is to iterate through each character of
each string in the array - if InStr(char, each_array_string)<>0, then
Replace(each_array_string, char, "") until I wind up with either all
zero-length strings because all the characters are duplicated in other
strings, or one string with one lone character.

Is there a smoother way?
Ed
(PS - gotta run; won't be back for a couple hours)

 

[Perry]

The only approach I can come up with is to iterate through each character

of
each string in the array - if InStr(char, each_array_string)<>0

I think you can't go "smoother" than yr above basic idea.
Repost some VBA code and maybe someone will be able counter a slight
adjustment of yr code to get it running.

 

[Joe in Australia via OfficeKB.com]

Here's a function and a subroutine that should do what you want. I haven't
added any error checking, so it will probably break if you try it with e.g.
an array of integers.


Option Explicit
Function Unique(ThisChar As String, MyArray As Variant) As Integer
' test to see if a substring is found in only element of an array.

Dim MyLoop As Integer: MyLoop = LBound(MyArray)
Dim TimesCharWasFound As Integer: TimesCharWasFound = 0

Do
If InStr(MyArray(MyLoop), ThisChar) <> 0 Then
Unique = MyLoop
TimesCharWasFound = TimesCharWasFound + 1
End If

MyLoop = MyLoop + 1

Loop While MyLoop <= UBound(MyArray)

If TimesCharWasFound <> 1 Then
Unique = -1
End If

End Function

Sub TestUnique()
' demonstration of function Unique() in operation. You can speed this up if
you aren't interested in checking (e.g.) non-alphabetic characters.

Dim MyReport As String: _
MyReport = ""
Dim MyLoop As Integer
Dim ArrayWithUnique As Integer

' This array has two unique characters.
Dim TestArray(1 To 3) As String: _
TestArray(1) = "boat": _
TestArray(2) = "coats": _
TestArray(3) = "crab"

' If you don't want to check for non-alphabetic characters then
' the clearest way of doing it would be to run several loops.
' Instead of one loop going from 0 to 255 have:
' one loop going from asc("a") to asc("z")
' and one going from asc("A") to asc("Z").
' You can do the same with numbers: asc("0") to asc("9")

For MyLoop = 0 To 255
ArrayWithUnique = Unique(Chr$(MyLoop), TestArray)
If ArrayWithUnique <> -1 Then
MyReport = _
MyReport _
& vbCr _
& "Character no. " _
& Str$(MyLoop) _
& " is uniquely found in array no. " _
& Str$(ArrayWithUnique)
End If
Next MyLoop

If MyReport = "" Then
MsgBox "There is no array with a unique character."
Else
MsgBox MyReport
End If

End Sub

 

[Ed]

Joe, that is awesome! Thank you so much!!

Ed

 

[Helmut Weber]

Hi everybody,

join and split can be helpful, too.

I assume, that there is a unique character:

Sub test0998()
Dim sAr1(1 To 10) As String
Dim sAr2() As String
Dim sLng As String
Dim c As Long
Dim l As Long
sAr1(1) = "boat"
sAr1(2) = "coats"
sAr1(3) = "crab"
sAr1(4) = "heaven"
sAr1(5) = "knish"
sAr1(6) = "fox"
sAr1(7) = "yellow"
sAr1(8) = "bush"
sAr1(9) = "schulzh"
sAr1(10) = "beethoven"
' --- main
sLng = Join(sAr1, Chr(5)) ' a long string
For c = 32 To 255
sAr2 = Split(sLng, Chr(c))
If UBound(sAr2) = 1 Then Exit For
' first unique character
Next
For l = 1 To 10
' first word containing that character
If InStr(sAr1(l), Chr(c)) Then Exit For
Next
' --- main
MsgBox Chr(c) & " in " & sAr1(l)

End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Joe in Australia via OfficeKB.com]

Hi everybody,

join and split can be helpful, too.

Wow, nice one! I like the way you shifted the test into the inbuilt functions.
I'd hate to have to maintain your code, though. Hardcoded variables and
things like "For l = 1 to ..." make it hard to follow.

jds

 

[Helmut Weber]

Hi Joe,

Wow, nice one!

I'd hate to have to maintain your code, though. (1)
Hardcoded variables and
things like "For l = 1 to ..." make it hard to follow. (2)

You are certanly right about (2).
I put it in in a quick and dirty way,
just to garnish my main idea a bit.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"