Invert a column of data.

[Mike 215]

I have a sheet 24 columns by 5000 rows.
I need to invert all of the data.
Transpose turns all data 90 degree, then will turn it back to original
layout.
I can do it one cell at a time.. ie in AA1 =A5000 etc.

Is there a way to do this all at once, or even 1 column at a time?

Thanks for your help.
Mike

 

[Pete_UK]

As you have less than 26 columns, you can do it with one formula. Put
this in AA1:

=INDIRECT(CHAR(64+COLUMN(A1))&5001-ROW(A1))

The 5001 is one more than the number of rows that you have - adjust to
suit your exact requirements. Then you can copy this across into
AB1:AX1, and then you can copy AA1:AX1 down to the bottom of your
data.

Once done, you can fix the values in that formula.

Another way would be to fill a simple sequence 1, 2, 3 etc down a
helper column, eg column Y. Then highlight all the data including the
helper column and use Data | Sort with the helper column as the sort
field and sort in descending order. You can remove the helper column
when finished.

Hope this helps.

Pete

 

[Mike 215]

As you have less than 26 columns, you can do it with one formula. Put
this in AA1:

=INDIRECT(CHAR(64+COLUMN(A1))&5001-ROW(A1))

The 5001 is one more than the number of rows that you have - adjust to
suit your exact requirements. Then you can copy this across into
AB1:AX1, and then you can copy AA1:AX1 down to the bottom of your
data.

Once done, you can fix the values in that formula.

Another way would be to fill a simple sequence 1, 2, 3 etc down a
helper column, eg column Y. Then highlight all the data including the
helper column and use Data | Sort with the helper column as the sort
field and sort in descending order. You can remove the helper column
when finished.

Hope this helps.

Pete





- Show quoted text -

Thank You Pete
Works like a charm
Mike

 

[Pete_UK]

You're welcome, Mike - thanks for feeding back.

Pete

 

[Sean Timmons]

why not just do copy, paste special/transpose?

As you have less than 26 columns, you can do it with one formula. Put
this in AA1:

=INDIRECT(CHAR(64+COLUMN(A1))&5001-ROW(A1))

The 5001 is one more than the number of rows that you have - adjust to
suit your exact requirements. Then you can copy this across into
AB1:AX1, and then you can copy AA1:AX1 down to the bottom of your
data.

Once done, you can fix the values in that formula.

Another way would be to fill a simple sequence 1, 2, 3 etc down a
helper column, eg column Y. Then highlight all the data including the
helper column and use Data | Sort with the helper column as the sort
field and sort in descending order. You can remove the helper column
when finished.

Hope this helps.

Pete