Invert a column of data.

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
 
P

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
 
M

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
 
S

Sean Timmons

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top