Invert a column of data.

  • Thread starter Thread starter Mike 215
  • Start date Start date
M

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
 
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
 
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 -
Click to expand...

Thank You Pete
Works like a charm
Mike
 
why not just do copy, paste special/transpose?

Pete_UK said:
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
Click to expand...
 
Back
Top