[EXCEL] Transform rows into columns
Solved
logan138
Posted messages
13
Registration date
Status
Membre
Last intervention
-
Aim -
Aim -
Hello,
I am looking to transform rows from my Excel spreadsheet into columns.
I tried using the TRANSFORM() function in array form, but it only partially addresses my issue.
1) There is no linkage between the cells. So if I modify the original cell(s), the "transformed" cell(s) do not change.
2) If I add data to my original table, I cannot perform "incremental copying" in my transposed table.
I hope I have been clear enough and that I haven’t gotten too mixed up. Since this is my first post, I hope I have been clear.
Thank you in advance everyone
Logan
small example
I am looking to transform rows from my Excel spreadsheet into columns.
I tried using the TRANSFORM() function in array form, but it only partially addresses my issue.
1) There is no linkage between the cells. So if I modify the original cell(s), the "transformed" cell(s) do not change.
2) If I add data to my original table, I cannot perform "incremental copying" in my transposed table.
I hope I have been clear enough and that I haven’t gotten too mixed up. Since this is my first post, I hope I have been clear.
Thank you in advance everyone
Logan
small example
Original table A B C 1 10.9943 10.983975 10.983325 Transposed table D 1 10.9943 2 10.983975 3 10.983325
Configuration: Windows XP Firefox 2.0.0.11 Excel 2003
11 réponses
to take the origin of the post with a few modifications
small example
Original table
A B C D
176 177 178
Julien 10 20 30
Aurélien 30 40 50
Benjamin 60 70 80
Transposed table in Sheet2
A B C
Julien 176 10
Julien 177 20
Julien 178 30
Aurélien 176 40
Aurélien 177 50
Aurélien 178 60
Benjamin 176 70
Benjamin 177 80
Benjamin 178 90
Thank you for your response
PS how can we post a .xls document?
small example
Original table
A B C D
176 177 178
Julien 10 20 30
Aurélien 30 40 50
Benjamin 60 70 80
Transposed table in Sheet2
A B C
Julien 176 10
Julien 177 20
Julien 178 30
Aurélien 176 40
Aurélien 177 50
Aurélien 178 60
Benjamin 176 70
Benjamin 177 80
Benjamin 178 90
Thank you for your response
PS how can we post a .xls document?
uh ....
it seems all pretty complicated ....
I don't know anything about it .... :):):)
Is there a simpler method ????
like with functions ....
because this looks like programming and I totally don't master that
maybe I posted in the wrong forum
thank you again to those who responded and to those who will respond
it seems all pretty complicated ....
I don't know anything about it .... :):):)
Is there a simpler method ????
like with functions ....
because this looks like programming and I totally don't master that
maybe I posted in the wrong forum
thank you again to those who responded and to those who will respond
Hello
I am looking to download Excel, but where should I go?
If anyone has an idea, thank you in advance, Marc.
I am looking to download Excel, but where should I go?
If anyone has an idea, thank you in advance, Marc.
Hello
Phil 32, your program doesn't work on my Excel sheet
So how should I program it?
Thank you for getting back to me
Phil 32, your program doesn't work on my Excel sheet
So how should I program it?
Thank you for getting back to me
Sub Transpose()
Dim Col As Range
For Each Col In Range("Transpose").Columns
Col.Copy Destination:=Worksheets("Sheet2").Range("A" & Col.Row)
Next
End Sub
Dim Col As Range
For Each Col In Range("Transpose").Columns
Col.Copy Destination:=Worksheets("Sheet2").Range("A" & Col.Row)
Next
End Sub
Could you provide more information, please? Because right now, I'm not sure I understand your response...
You go to Insert --> Name --> Define to define a range
For Each Col In Range("Transpose").Columns ' Logically here we "walk" from cell to cell from the beginning to the end.
Col.Copy Destination:=Worksheets("Sheet2").Range("A" & Col.Row) ' Sorry, I didn't see that we copy each column to the first column (i.e. "A")
that is a range B3, C3, D3. B3 is the first column of the range.
content of cell B3 --> A1
content of cell C3 --> A2
content of cell D3 --> A3
of course you have to adapt this code to your needs
For Each Col In Range("Transpose").Columns ' Logically here we "walk" from cell to cell from the beginning to the end.
Col.Copy Destination:=Worksheets("Sheet2").Range("A" & Col.Row) ' Sorry, I didn't see that we copy each column to the first column (i.e. "A")
that is a range B3, C3, D3. B3 is the first column of the range.
content of cell B3 --> A1
content of cell C3 --> A2
content of cell D3 --> A3
of course you have to adapt this code to your needs
Have you tried
" =INDEX($a$1:$ZZ$30,COLUMN(A1),ROW(A1)
) "?
You need to adjust the source table and the reference cell as needed, but it at least links the original table to the starting table as requested in the initial question. (instead of messing around with macros)
" =INDEX($a$1:$ZZ$30,COLUMN(A1),ROW(A1)
) "?
You need to adjust the source table and the reference cell as needed, but it at least links the original table to the starting table as requested in the initial question. (instead of messing around with macros)
Thank you very much