View original (French)

[EXCEL] Transform rows into columns

Solved
logan138 Posted messages 13 Registration date   Status Member Last intervention   -  
 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 

 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 answers

  1. jf
     
    Hello,

    to transpose rows to columns and vice versa very simply (if I understood the basic question correctly):

    select your table, copy.

    In another place (another sheet, for example): edit/special paste: check transpose, click ok.

    it should work.
    217
    1. logan138
       
      Yeah, true, but that doesn’t solve the question of the link between the values before and after transposition...
      0
    2. PM
       
      I tried, it's perfect!
      Thank you very much
      0
    3. Bijnok
       
      Does not work in Excel 2007.
      0
    4. YOUCEF
       
      a big thank you to you,
      0
    5. Isabelle Tardif
       
      Extraordinary! You've changed my life ;-)
      0
  2. intact saphir Posted messages 2 Status Member 6
     
    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?

    5
    1. Snouk
       
      Great! I struggled for an hour when it was so simple...
      Thank you
      0
  3. logan138 Posted messages 13 Registration date   Status Member Last intervention   4
     
    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
    2
  4. intact saphir
     
    Hello
    I am interested in this little program but I can't get it to work despite strictly copying it and adapting the data. Can you help me?
    2
    1. Matrix
       
      Hi,
      To answer your question, DSUM is a function in Excel - like Sum().
      So you use it without anything else.
      Take care.
      0
  6. marco
     
    Hello
    I am looking to download Excel, but where should I go?
    If anyone has an idea, thank you in advance, Marc.
    2
    1. Microsoft Corp
       
      Well, buy it!
      0
    2. Grebz
       
      You might as well use the free alternatives; you have the choice between Open Office, Libre Office, and IBM Lotus Symphony. It’s smarter than pirating.
      0
    3. kukuxu
       
      Learn to write. Also, buy Word.
      0
    4. marie
       
      There is no "s" in the verb "acheter" conjugated in the 2nd person of the imperative, dear kukuxu.
      0
    5. Olivier > marie
       
      Actually, yes, it all depends on when you learned it in school; 50 years ago, my father learned it like that. Moreover, according to "Le Conjugueur," that's actually how "Apprendre" is conjugated in the 2nd person singular, in the imperative ;)
      0
  7. intact saphir Posted messages 2 Status Member 6
     
    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
    1
  8. phil232 Posted messages 610 Status Member 178
     
    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
    0
  9. logan138 Posted messages 13 Registration date   Status Member Last intervention   4
     
    Could you provide more information, please? Because right now, I'm not sure I understand your response...
    0
  10. phil232 Posted messages 610 Status Member 178
     
    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
    0
  11. Turbodédé
     
    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)
    0
  12. marwenov
     
    There is a method, but you need to install MATLAB first.
    First, copy the matrix into MATLAB
    then
    name a matrix
    e.g.:
    m1=[matrix xl]
    then m1'
    copy the result into Excel.
    -1
    1. logan138 Posted messages 13 Registration date   Status Member Last intervention   4
       
      I don't understand what you mean. Could you go into more detail? I don't know how Mathlab works.
      0