PASEL
Level 1 (4 points)
Desktops

Q: Transpose Row / Columns Entries for Matrix

Hi All,

 

Read a few pages on the community forums relating to me little challenge, but came to the conclusion that using OFFSET would be the way to go, but I am struggling with the syntax. Maybe, I am over thinking this one (note: similar to copy n paste + transpose in excel). The idea is to, first, build the framework of the matrix using information from table 1 and 2 below and then laster, populate the inter connecting cells with data from table 2.

 

The first step is to transpose the first two columns of Table 2 to the top two columns of table one (as highlighted) that is DE-2 + it's main reference, DE-3 and it's main reference, and so on.

 

The number of entries in table 2 will increase with time, so would like this to be easy to update with simple infill.

 

Played with "OFFSET" without any success - Based on a similar problem posted on earlier version of numbers / iWorks. So, I am convinced it can be done this way - Any other ideas or suggestions would very welcome.

 

Thank you in advance

 

Perry

 

Screen Shot 2016-08-25 at 11.34.43.png

MacBook Pro, Mac OS X (10.7.5)

Posted on Aug 25, 2016 4:07 AM

by PASEL,Solvedanswer
PASEL PASEL
Level 1 (4 points)
Desktops
A:

HI SG,

 

Yes, I have tried the index solution that you proposed and as you stated does work. It depends what you mean by wide. At present the table will be 3146 (down) x 2500 (wide) - Will this be too ambitious?

 

Thank you for the response.

 

P

Posted on Aug 25, 2016 9:24 AM

Close
PASEL

Q: Transpose Row / Columns Entries for Matrix

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 25, 2016 4:56 AM in response to PASEL
    Level 6 (19,421 points)
    iWork
    Aug 25, 2016 4:56 AM in response to PASEL

    select the source table (botttom right in your screenshot), then select the menu item "Table >Transpose Rows & Columns",

     

    now select the area you want to move, copy

    now select the destination, paste

  • by SGIII,

    SGIII SGIII Aug 25, 2016 6:56 AM in response to PASEL
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 6:56 AM in response to PASEL

    If you want to be able to just add rows to Table 2 and have Table 1 automatically update without having to manually transpose, you can do this via formula like this:

     

    Screen Shot 2016-08-25 at 9.53.29 AM.png

     

    The formula in D1, filled right is:

     

       =INDEX(Table 2::$A,COLUMN()−2)

     

    The formula in D2, filled right, is:

     

       =INDEX(Table 2::$B,COLUMN()−2)

     

    SG

  • by PASEL,

    PASEL PASEL Aug 25, 2016 7:12 AM in response to Wayne Contello
    Level 1 (4 points)
    Desktops
    Aug 25, 2016 7:12 AM in response to Wayne Contello

    Thank you Wayne,

     

    I should have mentioned that I was trying to do this automatically as Table 2 will change - there will be new entries and some entries maybe disappear - Apologies for not making that clear.

     

    However, I really like how slick the manual transpose works with Numbers.

     

    P

  • by SGIII,

    SGIII SGIII Aug 25, 2016 7:21 AM in response to PASEL
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 7:21 AM in response to PASEL

    PASEL wrote:

     

     

    I should have mentioned that I was trying to do this automatically as Table 2 will change - there will be new entries and some entries maybe disappear -

     

    Did you see the suggested formula solution using INDEX?  I think it does what you want, and it's not hard to apply.

     

    Bear in mind that you don't want TOO many columns in Table 1.  Numbers gets a little awkward with "wide" tables.

     

    SG

  • by PASEL,Solvedanswer

    PASEL PASEL Aug 25, 2016 9:24 AM in response to SGIII
    Level 1 (4 points)
    Desktops
    Aug 25, 2016 9:24 AM in response to SGIII

    HI SG,

     

    Yes, I have tried the index solution that you proposed and as you stated does work. It depends what you mean by wide. At present the table will be 3146 (down) x 2500 (wide) - Will this be too ambitious?

     

    Thank you for the response.

     

    P

  • by SGIII,

    SGIII SGIII Aug 25, 2016 9:30 AM in response to PASEL
    Level 6 (10,796 points)
    Mac OS X
    Aug 25, 2016 9:30 AM in response to PASEL

    PASEL wrote:

     

    HI SG,

     

    Yes, I have tried the index solution that you proposed and as you stated does work. It depends what you mean by wide. At present the table will be 3146 (down) x 2500 (wide) - Will this be too ambitious?

     

     

    Numbers technically can handle 256 columns but in practice you won't want nearly that many.  Depending on the formulas you use, thousands of rows usually is not a problem.  It's not designed to handle very large datasets.

     

    SG