Rotating an array (but not transposing it)

[Grumpy]

I have a large array that I want to programmatically rotate clockwise
90 degrees.

Let me elaborate with a simple example. Let's say I'm starting off with
a 4x5 array that looks like the following:

A B C D E
1 M7 L7 K7 J7 H7
2 M5 L5 K5 J5 H5
3 M3 L3 K3 J3 H3
4 M1 L1 K1 J1 H1

I'd like to find a way to rotate the data in the array 90 degrees so
that I end up with a 5x4 array that looks like this::

A B C D
1 M1 M3 M5 M7
2 L1 L3 L5 L7
3 K1 K3 K5 K7
4 J1 J3 J5 J7
5 H1 H3 H5 H7

How can I do this without having to manually cut and paste each cell?
NOTE: Transpose is not the answer. If I used TRANSPOSE, the resulting
array would look like this:

A B C D
1 M7 M5 M3 M1
2 L7 L5 L3 L1
3 K7 K5 K3 K1
4 J7 J5 J3 J1
5 H7 H5 H3 H1

That's not the result I'm looking for!

Thanks in advance for any help anyone can provide me on this one.

Grumpy Grandpa

 

[Harlan Grove]

Grumpy wrote...

I have a large array that I want to programmatically rotate clockwise
90 degrees.

Let me elaborate with a simple example. Let's say I'm starting off with
a 4x5 array that looks like the following:

A B C D E
1 M7 L7 K7 J7 H7
2 M5 L5 K5 J5 H5
3 M3 L3 K3 J3 H3
4 M1 L1 K1 J1 H1

I'd like to find a way to rotate the data in the array 90 degrees so
that I end up with a 5x4 array that looks like this::

A B C D
1 M1 M3 M5 M7
2 L1 L3 L5 L7
3 K1 K3 K5 K7
4 J1 J3 J5 J7
5 H1 H3 H5 H7

How can I do this without having to manually cut and paste each cell?

....

You can't do it in-place without macros.

For my own convenience, I'll refer to the range above as X.

If you could accept using one formula per result cell, and if the
resulting matrix were supposed to be in A610, select A610 with A6
the active cell, type

=INDEX(X,ROWS(X)+1-COLUMNS($A6:A6),ROWS(A$6:A6))

hold down a [Ctrl] key and press [Enter].

If you believe you must do it as a single array formula or you need to
use the result as a term in another formula, and *IF* all entries in
your source matrix were text, select A1216, type

=T(OFFSET(X,ROWS(X)-1-TRANSPOSE(ROW(X))+MIN(ROW(X)),
TRANSPOSE(COLUMN(X))-MIN(COLUMN(X)),1,1))

hold down [Ctrl] and [Shift] keys and press [Enter]. If all the entries
were numbers, replace the T(..) call with an N(..) call. If the entries
were a mix of different types, there's no way to do this using only
built-in functions.