Excel Sorting a 2-Dimensional Array
Hi,
I have a VBA routine which gives the following results for your
latest data:
MaCaulay MacArthur MacDonald
McAdoo McCarran McCarthy
McClellan McClintock McClure
McCormick McCoy McCracken
McCullers McCulloch McDiarmid
As far as I aware, the solution will have to be done using VBA rather than
formulae.
HTH
" wrote:
Mr. Ogilvy,
Thank you for your response to my query.
1. You are correct to infer that in a practical application, the cells
are not confined to contain single letters only.
2. Furthermore, the data range (located on a worksheet) would encompass
Names, Coded Identification Numbers and (meaningful) Expressions
(consisting of multiple words).
3. In an application (that's akin to reality), I have attempted to
sort a collection of names such as Mac's and Mc's delineated as
follows :-
4. A1:C1 = McAdoo, MacArthur, MaCaulay
5. A2:C2 = McCarran, McCarthy, McClellan
6. A3:C3 = McClintock, McClure, McCormick
7. A4:C4 = McCoy, McCracken, McCullers
8. A5:C5 = McCulloch, McDiarmid, MacDonald
9. Although the formula,
{=CHAR(LARGE(CODE($A$1:$C$5),COUNTA($A$1:$C$5)-
(COLUMN()-COLUMN($E$1))-(ROW()-ROW($A$1))*COUNTA($A1:$C1)))},
does not scale (meaning that extending application from single letters
to full words but in vain), it has provided a starting point.
10. Hopefully, when the formula is modified to suit, a practical
solution will be in sight.
11. Regards.
|