HELP: flipping up/down or left/right

  • Thread starter Thread starter Skip Albertson
  • Start date Start date
S

Skip Albertson

Can anyone tell me the best way to flip a column up/down or a row
left/right (I'm borrowing the 'flip' term from MATLAB)?

For example, say I have 100 items in column A and I want column B to
contain them in reverse order (i.e., B1 = A100, B2 = A99 etc.).

Thanks in advance!
 
One way to reverse any set of numbers is to put the numbers 1 to 100 in the next column, select
both columns of data and sort on the second in descending order.
 
...
...
For example, say I have 100 items in column A and I want column B to
contain them in reverse order (i.e., B1 = A100, B2 = A99 etc.).

Alternatives to the INDIRECT already given.

B1:
=INDEX(A$1:A$100,101-ROW()) and filled down.

B1:B100:
=OFFSET(A$1,100-ROW(A$1:A$100),0,1,1) entered as an array formula.

B1:B100:
=MMULT(--(ROW(A$1:A$100)+TRANSPOSE(ROW(A$1:A$100))=ROWS(A$1:A$100)+1),A$1:A$100)

The first is most efficient and flexible. The second is less flexible, but
offers some interesting twists with 2D arrays. The last is least efficient, but
illustrates that this is an orthonormal transformation of the vector in A1:A100.
 
Back
Top