Re : Excel Sorting a 2-Dimensional Array

1. Enter an Excel worksheet.

2. Enter into the range A1 : C1 = A, C, E.
3. Enter into the range A2 : C2 = B, U, T.
4. Enter into the range A3 : C3 = C, O, P.
5. Enter into the range A4 : C4 = D, I, G.
6. Enter into the range A5 : C5 = E, L, F.

7. Enter a formula into every cell of E1:G5 such that the 2-dimensional
array of A1:C5 would be sorted (alphabetically in the horizontal order,
row-by-row) and displayed thereof. Please show the formulae.

8. Also, sort the 2-dimensional array by showing the unique items only.

9. Please share your comment. Regards.

Sort on what column.

all your rows appear unique. But, if they were not, define unique. Unique
in the first column, unique in 3 columns, what?

Why not just copy the data and use the built in sort function?

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Re : Excel Sorting a 2-Dimensional Array

1. Enter an Excel worksheet.

2. Enter into the range A1 : C1 = A, C, E.
3. Enter into the range A2 : C2 = B, U, T.
4. Enter into the range A3 : C3 = C, O, P.
5. Enter into the range A4 : C4 = D, I, G.
6. Enter into the range A5 : C5 = E, L, F.

7. Enter a formula into every cell of E1:G5 such that the 2-dimensional
array of A1:C5 would be sorted (alphabetically in the horizontal order,
row-by-row) and displayed thereof. Please show the formulae.

8. Also, sort the 2-dimensional array by showing the unique items only.

9. Please share your comment. Regards.

Mr. Ogilvy,

Thank you for your response to my query . Please allow me to explain
the query further.

1. Enter into the range A1 : C1 = A, C, E. It means that A1 contains
the letter A ; B1 contains the letter B ; C1 contains the letter E.

2. Similarly, A2=B, B2=U and C2=T, etc.

3. After the array, as shown in the range of cells A1:C5, is sorted in
general, it would display in the range of cells E1:G5 as follows :-

4. E1:G1 = A, B, C
5. E2:G2 = C, D, E
6. E3:G3 = E, F, G
7. E4:G4 = I, L, O
8. E5:G5 = P, T, U ; by virtue of the formulae (albeit yet to be
devised) entered into the respective cells.

9. And then, the unique items would be sorted as follows :-

10. E1:G1 = A, B, C
11. E2:G2 = D, E, F
12. E3:G3 = G, I, L,
13. E4:G4 = O, P, T
14. E5:G5 = U, <blank, <blank

15. Thereafter, changes entered into the range A1:C5 will be followed
suit correspondingly in the range E1:G5.

16. There is in practice, a similar requirement to manipulate but
larger arrays at the workplace ; that is relatively more involved than
the ABC-illustration given hereinbefore. A simplified illustration of
the requirement should readily invite a solution.

17. Regards.

Hi,
For the first part of your query try the following:

Enter

=CHAR(LARGE(CODE($A1:$C1),COUNTA(A1:$C1)))

into E1 as an array formula - use Shift-Ctrl-Enter. Drag into cells F1,G1
and drag down E1 to G1 to E5 to G5. This works over a larger range of cells.

This assumes the data is entered into contiguous cells and there are no
blanks.

HTH

" wrote:

Mr. Ogilvy,

Thank you for your response to my query . Please allow me to explain
the query further.

1. Enter into the range A1 : C1 = A, C, E. It means that A1 contains
the letter A ; B1 contains the letter B ; C1 contains the letter E.

2. Similarly, A2=B, B2=U and C2=T, etc.

3. After the array, as shown in the range of cells A1:C5, is sorted in
general, it would display in the range of cells E1:G5 as follows :-

4. E1:G1 = A, B, C
5. E2:G2 = C, D, E
6. E3:G3 = E, F, G
7. E4:G4 = I, L, O
8. E5:G5 = P, T, U ; by virtue of the formulae (albeit yet to be
devised) entered into the respective cells.

9. And then, the unique items would be sorted as follows :-

10. E1:G1 = A, B, C
11. E2:G2 = D, E, F
12. E3:G3 = G, I, L,
13. E4:G4 = O, P, T
14. E5:G5 = U, <blank, <blank

15. Thereafter, changes entered into the range A1:C5 will be followed
suit correspondingly in the range E1:G5.

16. There is in practice, a similar requirement to manipulate but
larger arrays at the workplace ; that is relatively more involved than
the ABC-illustration given hereinbefore. A simplified illustration of
the requirement should readily invite a solution.

17. Regards.

Mr. Toppers,

Thank you for your response to my query.

I have attempted upon your suggestion of a solution and, after
array-entered the formula

{=CHAR(LARGE(CODE($A1:$C1),COUNTA(A1:$C1)))},

into every cell of the range E1:G5, the results appear to be as follows
:-
1. E1:G1 = A, C, E
2. E2:G2 = B, T, U
3. E3:G3 = C, O, P
4. E4:G4 = D, G, I
5. E5:G5 = E, F, L

6. And so, it's apparently a deviation from the desired results as
given below :-

7. E1:G1 = A, B, C
8. E2:G2 = C, D, E
9. E3:G3 = E, F, G
10. E4:G4 = I, L, O
11. E5:G5 = P, T, U

12. Please note that the general sorting (in alphabetical order) ought
to consider the group of entries in the range A1:C5 as a whole ;
instead of the piece-wise attempt (row-by-row) as portrayed by your
formula.

13. Regards.

Hi,

Try again!

=CHAR(LARGE(CODE($A$1:$C$5),COUNTA($A$1:$C$5)-(COLUMN()-COLUMN($E$1))-(ROW()-ROW($A$1))*COUNTA($A1:$C1)))

This does NOT remove the duplicates!

I think you may have to resort to VBA code to solve this.



" wrote:

Mr. Toppers,

Thank you for your response to my query.

I have attempted upon your suggestion of a solution and, after
array-entered the formula

{=CHAR(LARGE(CODE($A1:$C1),COUNTA(A1:$C1)))},

into every cell of the range E1:G5, the results appear to be as follows
:-
1. E1:G1 = A, C, E
2. E2:G2 = B, T, U
3. E3:G3 = C, O, P
4. E4:G4 = D, G, I
5. E5:G5 = E, F, L

6. And so, it's apparently a deviation from the desired results as
given below :-

7. E1:G1 = A, B, C
8. E2:G2 = C, D, E
9. E3:G3 = E, F, G
10. E4:G4 = I, L, O
11. E5:G5 = P, T, U

12. Please note that the general sorting (in alphabetical order) ought
to consider the group of entries in the range A1:C5 as a whole ;
instead of the piece-wise attempt (row-by-row) as portrayed by your
formula.

13. Regards.

Will your real application only be single letters in the cells?

If not, this approach does not scale.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Mr. Toppers,

Thank you for your response to my query.

I have attempted upon your suggestion of a solution and, after
array-entered the formula

{=CHAR(LARGE(CODE($A1:$C1),COUNTA(A1:$C1)))},

into every cell of the range E1:G5, the results appear to be as follows
:-
1. E1:G1 = A, C, E
2. E2:G2 = B, T, U
3. E3:G3 = C, O, P
4. E4:G4 = D, G, I
5. E5:G5 = E, F, L

6. And so, it's apparently a deviation from the desired results as
given below :-

7. E1:G1 = A, B, C
8. E2:G2 = C, D, E
9. E3:G3 = E, F, G
10. E4:G4 = I, L, O
11. E5:G5 = P, T, U

12. Please note that the general sorting (in alphabetical order) ought
to consider the group of entries in the range A1:C5 as a whole ;
instead of the piece-wise attempt (row-by-row) as portrayed by your
formula.

13. Regards.

Mr. Toppers,

Thank you for your response to my query.

I have attempted upon your suggestion of a solution by array-entering
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)))},

into every cell of the range E1:G5, the results appear to be as follows
:-

1. E1:G1 = A, B, C
2. E2:G2 = C, D, E
3. E3:G3 = E, F, G
4. E4:G4 = I, L, O
5. E5:G5 = P, T, U

6. The solution is indeed meeting the requirement as delineated by this
query.

7. However, I could not help it but to mention again that the practical
requirement is relatively more involved than the ABC-illustration given
hereinbefore.

8. Notwithstanding, the technique of indexing into a 2-dimensional
array (as deployed by your formula) is commendable. That will suffice a
testimony of larger calibre capable of meeting other complex conditions
in practical application.

9. Later, I would like to reply to the comment given by Mr. Ogilvy.

10. Regards.

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.

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.

Further to my last note, I now have User-Defined Function (UDF) which I hope
meets your needs.

"Toppers" wrote:

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.

Mr. Toppers,

Thank you for delving into extra miles with persevering pursuit ;
that's surely adding up considerable time and effort expended.

You may wish to show your UDF for the general benefits of the
interested participants in this neighbourhood. However, I should hasten
to dispel the notion that there's an ulterior motive clad in the name
of public needs.

For my personal interests, I would like to learn of your deduction, in
logical steps, leading to the development of the UDF as a necessary
extension to MS-Excel's (already-rich) repertoire of worksheet
functions.

Regards.