Re : Excel Sorting a 2-Dimensional Array

[tkt_tang]

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.

 

[Tom Ogilvy]

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?

 

[tkt_tang]

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.

 

[Toppers]

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

 

[tkt_tang]

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.

 

[Toppers]

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.

 

[Tom Ogilvy]

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

If not, this approach does not scale.

 

[tkt_tang]

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.

 

[tkt_tang]

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.

 

[Toppers]

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

 

[Toppers]

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

 

[tkt_tang]

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.