I'm looking to create a function that will compare two
strings A and B, and return True if string A is "less than" B.
Uppercase and/or lowercase should be ignored.

For example:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

My input will consist of only words that begin with only
alphanumberic characters, but may contain hyphens (-) or
apostrophes (') within the word.

Since I will be comparing a LARGE set of words, I'm
hoping for a comparison function that works as quickly
as possible.

Thanks!

Hi Robert,

Am Fri, 15 May 2015 05:35:23 -0700 schrieb Robert Crandal:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

try:
Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next
Next
End Function

and call the function in the sheet with
=myComp(A1)

If nesessary you have to insert a Replace for hyphens and single quotes.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Fri, 15 May 2015 14:52:56 +0200 schrieb Claus Busch:

For Each rngC In myRng

the first For ... Next statement is absolutly superfluous:

Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Fri, 15 May 2015 14:59:03 +0200 schrieb Claus Busch:

For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

sorry but now rngC is wrong.

Try:

Function myComp(myRng As Range) As Boolean
Dim i As Long

For i = 1 To Len(myRng)
If LCase(Mid(myRng, i, 1)) < _
LCase(Mid(myRng.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(myRng, i, 1))) < _
Asc(LCase(Mid(myRng.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next

End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

Am Fri, 15 May 2015 05:35:23 -0700 schrieb Robert Crandal:

A B Value
--------- ------- -------
apple banana True
banana apple False
winner wins True
WiNneR winS True
right-handed left-handed False
apple apple False (equal)

try:
Function myComp(myRng As Range) As Boolean
Dim rngC As Range
Dim i As Long

For Each rngC In myRng
For i = 1 To Len(rngC)
If LCase(Mid(rngC, i, 1)) < _
LCase(Mid(rngC.Offset(, 1), i, 1)) Then
myComp = IIf(Asc(LCase(Mid(rngC, i, 1))) < _
Asc(LCase(Mid(rngC.Offset(, 1), i, 1))), True, False)
Exit For
End If
Next
Next
End Function

and call the function in the sheet with
=myComp(A1)


Hi Claus. Unfortunately, I will not be comparing strings that
exist on a spreadsheet. I will be comparings strings that are
stored in String variables. So, for example:

Dim strA as String
Dim strB as String

strA = "apple"
strB = "banana"

If myComp(strA, strB) = True then
'
End if

I apologize for the bad explanation. My table above looked
like a spreadsheet, but really it was just a sample chart of
example data. So, I need a function that does not rely on
data stored in a spreadsheet. So sorry about that.

Hi Robert,

Am Fri, 15 May 2015 06:03:54 -0700 schrieb Robert Crandal:

Hi Claus. Unfortunately, I will not be comparing strings that
exist on a spreadsheet. I will be comparings strings that are
stored in String variables. So, for example:

then try:

Sub CompStrings()
Dim strA As String, strB As String
Dim i As Long
Dim myComp As Boolean

strA = "right-handed"
strB = "left-handed"

strA = Replace(Replace(strA, "-", ""), "'", "")
strB = Replace(Replace(strB, "-", ""), "'", "")

For i = 1 To Len(strA)
If LCase(Mid(strA, i, 1)) < LCase(Mid(strB, i, 1)) Then
myComp = IIf(Asc(LCase(Mid(strA, i, 1))) < _
Asc(LCase(Mid(strB, i, 1))), True, False)
MsgBox myComp
Exit For
End If
Next

End Sub

If your strings are in a 2D Array you could loop through the array and
insert the result into it and then write the array to a spreadsheet.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

Sub CompStrings()

' snipped

End Sub


Thanks again Claus!

"Claus Busch" wrote:

Sub CompStrings()
Dim strA As String, strB As String
Dim i As Long
Dim myComp As Boolean

strA = "right-handed"
strB = "left-handed"

strA = Replace(Replace(strA, "-", ""), "'", "")
strB = Replace(Replace(strB, "-", ""), "'", "")

For i = 1 To Len(strA)
If LCase(Mid(strA, i, 1)) < LCase(Mid(strB, i, 1)) Then
myComp = IIf(Asc(LCase(Mid(strA, i, 1))) < _
Asc(LCase(Mid(strB, i, 1))), True, False)
MsgBox myComp
Exit For
End If
Next

End Sub


Claus, do you think it will be faster to use the "<" or "" operators
to compare strings?

For example:

strA = "left-handed"
strB = "right-handed"

if (strA < strB) then
MsgBox "True"
else
MsgBox "False"
end if

Hi Robert,

Am Sun, 17 May 2015 01:22:48 -0700 schrieb Robert Crandal:

Claus, do you think it will be faster to use the "<" or "" operators
to compare strings?

I don't have much experience working with text in Excel. But you are
right. You don't have to compare sign for sign, you can compare the
whole string.
Try:

Option Explicit
Option Compare Text

Sub StringCompare()
Dim StrA As String, strB As String

StrA = "left-handed"
strB = "right-handed"

If StrA < strB Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub

If you use
Option compare Binary
"AAA"< "aaa"
With Option Compare Text both are equal


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

If you use
Option compare Binary
"AAA"< "aaa"
With Option Compare Text both are equal


Nice! If I use "Option Compare Text", then
the following evalutes to true:

"AAA" = "aaa" ' True!

Thanks! I didnt know about that option.

Hi Robert,

Am Sun, 17 May 2015 01:53:18 -0700 schrieb Robert Crandal:

"AAA" = "aaa" ' True!

why do you want to compare the strings? Do you want to sort them?
If so and you have stored your strings in varOut (1D Array) then try:

For n = UBound(varOut) - 1 To LBound(varOut) Step -1
For i = LBound(varOut) To n
If varOut(i) varOut(i + 1) Then
strTemp = varOut(i)
varOut(i) = varOut(i + 1)
varOut(i + 1) = strTemp
End If
Next i
Next n

For i = LBound(varOut) To UBound(varOut)
Debug.Print varOut(i)
Next


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

why do you want to compare the strings? Do you want to sort them?
If so and you have stored your strings in varOut (1D Array) then try:


Actually, I am experimenting with an "insert sort". I want to insert
words, one at a time, into column A of my spreadsheet.
If I need to insert a word between two words, I am using the
Selection.Insert command to make a new row for the new word.

Do you have any suggestions on how to achieve this sort in
ascending order? For now I'm trying to perform an "insert sort",
and column A will store all words after the sort is complete.

Hi Robert,

Am Tue, 19 May 2015 01:49:50 -0700 schrieb Robert Crandal:

Actually, I am experimenting with an "insert sort". I want to insert
words, one at a time, into column A of my spreadsheet.
If I need to insert a word between two words, I am using the
Selection.Insert command to make a new row for the new word.

Do you have any suggestions on how to achieve this sort in
ascending order? For now I'm trying to perform an "insert sort",
and column A will store all words after the sort is complete.

there are two fast ways.
One way is the bubblesort from my last reply. You sort into the array
and write the array to column A. The code you can insert into the
compare code.
The second way is to write the array first to column A and then sort
column A ascending.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

there are two fast ways.
One way is the bubblesort from my last reply. You sort into the array
and write the array to column A. The code you can insert into the
compare code.
The second way is to write the array first to column A and then sort
column A ascending.


I am looking into the idea of creating a spreadsheet that stores all the
words
of multiple documents, sorted in column A. The spreadsheet will grow bigger
over time, because I will keep adding more and more words each day.

If column A already contains a long list of sort words, do you recommend
that
I load those words into an array, add my new words to the array, do a quick
sort,
and then rewrite the sorted listed back to the spreadsheet?

Hi Robert,

Am Tue, 19 May 2015 02:24:23 -0700 schrieb Robert Crandal:

I am looking into the idea of creating a spreadsheet that stores all the
words
of multiple documents, sorted in column A. The spreadsheet will grow bigger
over time, because I will keep adding more and more words each day.

write the new word under the existing words, then sort the column and
remove duplicates. With the following code you only have to change the
path and file name of your txt. file. Or you write a loop over all .txt.
files:

Sub Test4()
'
'Writes all words to column A and then sort ascending
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<

'New 2015-05-09 CB
'Revised 2015-05-10 CB
'Revised 2015-05-19 CB

Dim strFN As String
Dim objReadFile As Object, myDic As Object, objFSO As Object, re As
Object
Dim strText As String, strTemp As String
Dim varText() As Variant, varOut As Variant, varTmp() As Variant
Dim i As Long, LRow As Long, n As Long
Dim ptrn, Match, Matches
Dim FERow As Range

Application.ScreenUpdating = False

'Modify path and file name
strFN = "C:\Data.txt"

Set myDic = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set re = CreateObject("vbscript.regexp")

'Opens the text file and read the text into a string
Set objReadFile = objFSO.opentextfile(strFN)
strText = objReadFile.readall
objReadFile.Close

'Separate all "words"
ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)
ReDim Preserve varText(Matches.Count - 1)
For Each Match In Matches
varText(n) = LCase(Match.Value)
n = n + 1
Next

Cells(Rows.Count, "A").End(xlUp)(2).Resize(UBound(varText) + 1) = _
Application.Transpose(varText)

With Range("A:A")
.Sort , key1:=Range("A1"), order1:=xlAscending, Header:=xlNo
.RemoveDuplicates Columns:=1, Header:=xlNo
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional