C
Captain_Nemo
To create a alphabetical sorting calculation via worksheet functions, I
took the following approach that requires nine scratch columns. I'd like
to find an array implementation.
Assume the text to be sorted is in Column "A". The eight Columns B:I
contain, filled right and down:
=IF(ISERROR(CODE(UPPER(MID($A1,n,1)))),"00",CODE(UPPER(MID($A1,n,1))))
Where "n" is COLUMN()-1. The MID()'s return a single letter from left
to right in order from $A1.
The UPPER() changes it to upper case, obviously, but more importantly
makes the CODE() return 2-digit ASCII.
The ISERROR() returns text "00" (zero-zero) when the CODE(UPPER(MID())
breaks because it can't handle letters beyond the length of $A1.
Column J contains:
=VALUE(LEFT(B1&C1&D1&E1&F1&G1&H1&I1,15))
The LEFT() returns the 15 left-most digits of the 16-character
concatenation of B:I. The VALUE() turns this into a number at Excel's
limit of significant digits that represents the word in $A1.
For example, "qwerty" becomes VALUE(LEFT(8187698284890000,15)) or
818769828489000 as a number. (Note the trailing zeroes from the
ISERROR()--this is what puts short, few-character, end-of-the-alphabet
words in the right place in the next step.)
Column K contains:
=INDEX(A$1:A$3077,MATCH(SMALL(J$1:J$3077,ROW()),J$1:J$3077,0),1)
ROW() returns 1, 2, 3 ... etc
SMALL() returns the 1st smallest, then the 2nd smallest, then the 3rd,
etc. from ROW()
MATCH(,,0) returns the row the exact match is found. Fill down, and
INDEX() returns the word from Column A in that row, and by gum, 3077
words are sorted. Obviously, only perfect through 7 letter words, and
extremely servicable thru eight and beyond. My test set of varying
length words (not a dictionary) matched exactly the Data Sort done by
menu except when there was punctuation within the word (ie
"alpha-sort").
So now, my challenge for the array wizards: Can some of this be done
without the scratch columns? An alternative but similar approach is to
have the IF(ISERROR()) return zero, and create the 15-significant-number
representation using
SUM(B1*10^-1,C1*10^-3,D1*10^-5,E1*10^-7,F1*10^-9,G1*10^-11,H1*10^-13,I1*1
0^-15) or a similar SUMPRODUCT.
The last significant digit was different 1/3 of the time. In one
approach, the 16th digit is truncated, in the other it rounds. Couldn't
find a different in results, but maybe in efficiency?
Thanks in advance.
....best, Capt N.
--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]
Scream and shout and jump for joy! I was here before Kilroy!
Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy
took the following approach that requires nine scratch columns. I'd like
to find an array implementation.
Assume the text to be sorted is in Column "A". The eight Columns B:I
contain, filled right and down:
=IF(ISERROR(CODE(UPPER(MID($A1,n,1)))),"00",CODE(UPPER(MID($A1,n,1))))
Where "n" is COLUMN()-1. The MID()'s return a single letter from left
to right in order from $A1.
The UPPER() changes it to upper case, obviously, but more importantly
makes the CODE() return 2-digit ASCII.
The ISERROR() returns text "00" (zero-zero) when the CODE(UPPER(MID())
breaks because it can't handle letters beyond the length of $A1.
Column J contains:
=VALUE(LEFT(B1&C1&D1&E1&F1&G1&H1&I1,15))
The LEFT() returns the 15 left-most digits of the 16-character
concatenation of B:I. The VALUE() turns this into a number at Excel's
limit of significant digits that represents the word in $A1.
For example, "qwerty" becomes VALUE(LEFT(8187698284890000,15)) or
818769828489000 as a number. (Note the trailing zeroes from the
ISERROR()--this is what puts short, few-character, end-of-the-alphabet
words in the right place in the next step.)
Column K contains:
=INDEX(A$1:A$3077,MATCH(SMALL(J$1:J$3077,ROW()),J$1:J$3077,0),1)
ROW() returns 1, 2, 3 ... etc
SMALL() returns the 1st smallest, then the 2nd smallest, then the 3rd,
etc. from ROW()
MATCH(,,0) returns the row the exact match is found. Fill down, and
INDEX() returns the word from Column A in that row, and by gum, 3077
words are sorted. Obviously, only perfect through 7 letter words, and
extremely servicable thru eight and beyond. My test set of varying
length words (not a dictionary) matched exactly the Data Sort done by
menu except when there was punctuation within the word (ie
"alpha-sort").
So now, my challenge for the array wizards: Can some of this be done
without the scratch columns? An alternative but similar approach is to
have the IF(ISERROR()) return zero, and create the 15-significant-number
representation using
SUM(B1*10^-1,C1*10^-3,D1*10^-5,E1*10^-7,F1*10^-9,G1*10^-11,H1*10^-13,I1*1
0^-15) or a similar SUMPRODUCT.
The last significant digit was different 1/3 of the time. In one
approach, the 16th digit is truncated, in the other it rounds. Couldn't
find a different in results, but maybe in efficiency?
Thanks in advance.
....best, Capt N.
--
Email to (e-mail address removed) (yes, you can so figure it out) ;-]
Scream and shout and jump for joy! I was here before Kilroy!
Sorry to spoil your little joke. I was here but my computer broke. ---Kilroy