Invert a column of data.
 

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

Invert a column of data.
 

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

On Aug 25, 2:20*pm, Mike 215 wrote:
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

Invert a column of data.
 

On Aug 25, 8:31*am, Pete_UK wrote:
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

On Aug 25, 2:20*pm, Mike 215 wrote:



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- Hide quoted text -

- Show quoted text -

Thank You Pete
Works like a charm
Mike

Invert a column of data.
 

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

Pete

On Aug 25, 2:39*pm, Mike 215 wrote:

Thank You Pete
Works like a charm
Mike

Invert a column of data.
 

why not just do copy, paste special/transpose?

"Pete_UK" wrote:

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

On Aug 25, 2:20 pm, Mike 215 wrote:
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