Sorting alphabetically

alexali alexali at surf.net.au
Wed Jul 28 19:19:40 EDT 1999


Wieland Willker asked some days ago about sorting alphabetically in Excel,
a question I saw some days after its posting as a result of my being
pressed for time.  Nevertheless, the following may be of assistance, being
alternative functions to insert into Excel (97) to accomplish the task. 
The first is based on a transliteration system which uses a single English
letter as equivalent for each Greek letter;  the second is based on a
transliteration which uses some double letters for certain Greek
characters, such as TH for theta.

I'm unsure of the exact transliteration scheme Wieland would prefer; 
Wieland, if you would like to provide it to me (off-list, perhaps), the
appropriate function could be suited specifically to your purpose.

In order to get the function to work (having opened up an Excel worksheet),

1.	Press the key combination ALT+F11
This will take you into the Visual Basic editor.

2.	From the menu at the top, select Insert.

3.	Then choose   Module

4.	Then copy the appropriate code into the module.

I'll give the code first before making some final remarks.

Public Function alpha(cref As String) As String
'GRK: sa b g d e z h q i k l m n x o p r s t u f c y w
'ENG: a b c d e f g h i j k l m n o p q r s t u v w x
'The GRK line shows the letters used in one particular transliteration
scheme.
'This transliteration scheme uses no paired English letters
'to represent Greek characters.
'The ENG line shows the letters to which the transliterations are
'to be shifted, in order to allow indexing in correct order.
'
alpha$ = ""
greekalpha$ = "abgdezhqiklmnxoprstufcyw"
engalpha$ = "abcdefghijklmnopqrstuvwx"
a$ = cref
lenwd = Len(a$)
'Change (final) sigma transliterated as "j" to "s"
finalsigma:
f = InStr(1, a$, "j", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 1) = "s"
    GoTo finalsigma '(Strictly, only one iteration should be required.)
End If
'Change the transliterations to corresponding alphabetical place holders
For x = 1 To lenwd
    Find$ = Mid(a$, x, 1)
    f = InStr(1, greekalpha$, Find$, vbTextCompare)
    If f > 0 Then
        alpha$ = alpha$ + Mid(engalpha$, f, 1)
    End If
Next x
End Function


Alternatively, use the following function.


Public Function dbl(wd As String) As String
'GRK: a b g d e z h TH i k l m n x o p r s t u PH CH PS w
'ENG: a b c d e f g h  i j k l m n o p q r s t  u  v w  x
'The GRK line shows the letters used in a transliteration scheme
'which uses some paired English letters to represent certain
'Greek characters, eg theta is represented by "th".
'(The capitals have no significance but to highlight
' transliterations which use paired letters.)
'The ENG line shows the letters to which the transliterations are
'to be shifted, in order to allow indexing in correct order.
'
dbl = ""
greekalpha$ = "abgdezhqiklmnxoprstufcyw"
engalpha$ = "abcdefghijklmnopqrstuvwx"
a$ = wd
lenwd = Len(a$)
'Change (final) sigma transliterated as "j" to "s"
finalsigma:
f = InStr(1, a$, "j", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 1) = "s"
    GoTo finalsigma
End If
thetatest:
'Change theta transliterated as "th" to "q ".  The space will be ignored.
f = InStr(1, a$, "th", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 2) = "q "
    GoTo thetatest
End If
chitest:
'Change chi transliterated as "ch" to "c ".  The space will be ignored.
f = InStr(1, a$, "ch", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 2) = "c "
    GoTo chitest
End If
phitest:
'Change phi transliterated as "ph" to "f ".  The space will be ignored.
f = InStr(1, a$, "ph", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 2) = "f "
    GoTo phitest
End If
psitest:
'Change psi transliterated as "ps" to "y ".  The space will be ignored.
f = InStr(1, a$, "ps", vbTextCompare)
If f > 0 Then
    Mid(a$, f, 2) = "y "
    GoTo psitest
End If
'Change the transliterations to corresponding alphabetical place holders
For x = 1 To lenwd
    Find$ = Mid(a$, x, 1)
    f = InStr(1, greekalpha$, Find$, vbTextCompare)
    If f > 0 Then
        dbl = dbl + Mid(engalpha$, f, 1)
    End If
Next x
End Function


These functions can then be used as any other Excel function.  If, for
example, you use the first of the functions (alpha) and put the value
"agaph" in cell A1, enter the formula "=alpha(A1)" in cell B1.   (Do not
type in the quotation marks in the formula.)   The formula will 'call' the
function and the value "acapg" will appear in cell B1, ready to be sorted
against other values treated in the same manner.  (Alternatively, using the
second function (dbl) enter the formula "=dbl(A1)".

Like any other Excel formula, you can copy it to another cell or range.  If
you use a Greek font which transliterates each Greek letter into a single
English keyboard equivalent, i.e. it does not use double letters such as TH
for theta, you can show the Greek word in its font in the desired cell,
making it easier to check your own typing of the Greek word.  (The first of
the functions, alpha, is based on the keyboard equivalents used by the
bwgrkn font provided with BibleWorks for Windows.)

My own experience is enough to make me aware how easily unforeseen errors
may lurk in code.  Please let me know if the either function does not work
as anticipated.

In my days as a university student, I became frustrated at the number of
times I would check the meaning of a Greek word in the lexicon (only to
forget it or its details and have to check again);  later, when wysiwyg
fonts arrived, I tired of trying to bend Access to suit my purposes in
handling Greek.  So I wrote a program in Visual Basic to handle sorting and
searching in Greek, allowing me to make my notes in a way better adapted to
the task than Excel or Access allows;  if any were interested, let me know.

Dr Alexander Hopkins   (Melbourne, Australia)




More information about the B-Greek mailing list