Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Invert a column | Excel Worksheet Functions | |||
invert Lines and column on Excel | Excel Discussion (Misc queries) | |||
reverse or invert column data | Excel Worksheet Functions | |||
how do you invert a column | Excel Discussion (Misc queries) | |||
How do I invert a column in Excel? | Excel Discussion (Misc queries) |