Skip navigation

transpose columns and rows in numbers

5340 Views 38 Replies Latest reply: Apr 18, 2014 9:57 AM by SGIII RSS
1 2 3 Previous Next
cdevitarun Calculating status...
Currently Being Moderated
Jul 29, 2013 11:10 AM

I need to transpose columns and rows in Numbers and I do not want to write script to do it.  Is there an easier way?

MacBook Pro, iOS 6.1.4, iWork - Numbers
  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Jul 29, 2013 11:48 AM (in response to cdevitarun)

    you can make a new table which contains the formula in cells to transpose.  This is generally employees the OFFSET() function.

     

    Once you have the new table and transposed information you can copy, then paste values using the menu item "Edit > Paste Values"

     

    Screen Shot 2013-07-29 at 1.45.58 PM.png

    The table on the left is the original data, the table on the right references the table "Original Data".

     

    In the table on the right:

    A1=OFFSET(Original Data :: $A$1, COLUMN()-1, ROW()-1, 1, 1)

     

    select A1 and fill to the right by selecting A1, then grab (click and hold) the little circle at the bottom right of the selection , then drag to the right as needed.

     

    Now copy the first row, then paste values where you need the data.

  • Jerrold Green1 Level 7 Level 7 (28,270 points)
    Currently Being Moderated
    Jul 29, 2013 12:51 PM (in response to cdevitarun)

    CD,

     

    There is a TRANSPOSE function, but it's not too useful in my experience, since you have to use INDEX to read the result out of internal memory. Below are two examples, the first using TRANSPOSE and INDEX and the second using only INDEX, but this time reading the input table directly rather than via the transpose function. Note that skipping the transpose function requires that you reverse the Row and Column arguments.

     

    In case the graphic is difficult to read, the first expression is:

     

    =INDEX(TRANSPOSE(INDIRECT("Input")), ROW(), COLUMN())

     

    and the second expression is:

     

    =INDEX(Input, COLUMN(), ROW()).

     

    Jerry

    Screen Shot 2013-07-29 at 3.43.14 pm.png

  • Cookie J Calculating status...
    Currently Being Moderated
    Dec 19, 2013 12:18 PM (in response to Jerrold Green1)

    Seriously....I must agree. While spreadsheets in general are perhaps not greatly utilized by Joe Average.  The absense of a ***simple*** transpose operation is baffling.  If you want to "rotate" a Picture in iPhoto... it's easy.   Apple makes it easy to rotate the page orientation in numbers....but I guess it never occurred to them that people would want to easily rotate all or some of the data in the table..  Now I can see when the data has a different shape this would be more problematic, however; rotating the same shape portion of a table or an entire table should be a cakewalk. Select...Click....done.

  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Dec 19, 2013 12:36 PM (in response to Cookie J)

    OK.  You can post feedback to Apple using the menu item "Numbers > Provide Numbers Feedback"

     

    I use spreasheets every day and have not missed this function.  So my opinion is different than yours.  You should always use the tool that works best for your needs.

     

    If you transpose often you can make a template so all you have to do is open it, paste the data into an input table, and copy out of an output table.  I rarely transpose so I just make it on occasion.

  • Eltham Jones Level 1 Level 1 (15 points)
    Currently Being Moderated
    Jan 14, 2014 4:33 PM (in response to Wayne Contello)

    Posting feedback to Apple is a waste of time Wayne. They aren't interested.

     

    The absence of a transposition function that ordinary people who aren't Excel boffins can use without third party AppleScripts or or laborious functions is a major shortcoming of Numbers and they are in no hurry to change it.

     

    I suggested to them two years ago that it would be as simple as implementing a "Paste as…" command in the Edit menu, with two options; "column" and "row". If a column had been copied you would be offered the option "Paste as row" and vice versa, with the default "Paste" command remaining in place if transposition wasn't required.

     

    You may not use transpose yourself, but much of the work I do with spreadsheets involves taking huge masses of undifferentiated data, sorting it, and presenting it in a way that makes it intelligible to non-technical people and the data I work with makes the transpose function not only useful, but essential.

     

    I had naïvely assumed that Apple's spreadsheet would be easier to use than Excel, whise implementation of transpose through the "paste special" command (which it seemingly inherited from Claris) is confusing enough but Apple seem to have contrived to make something that should be simple even more complicated.

     

    To me, like the execrable DVD player that has been bundled with OSX from the start but which, even now, still lacks the ability to change aspect ratio, it's just another example of Apple's remoteness form their users. It's corporate arrogance on a monumental scale and one of the reasons I'm seriously considering Linux alternatives. Even Apache Office has the ability to transpose. It's a spreadsheet basic.  Like everything they do at the moment, Numbers seems to place form over function and to **** with the end user.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jan 14, 2014 5:23 PM (in response to Eltham Jones)

    Hi EJ,

     

    This is a user-to-user forum. Here users of the software that is the subject of the forum can offer advice on how to work with the software as it currenty exists. Participants in this forum are not the developers of the software, nor are they, as a general rule, employees of Apple.

     

    If your need is to have the software altered to fit your purposes, then this is not the audience to whom you should be addressing your request. Hence the advice to Provide Numbers Feedback, using the link with that name, found in the Numbers menu in Numbers.

     

    Another piece of advice that has often been offered here may also fit your situation:

     

    Use the tool that best does the job you need to do. If Transpose is better done in MS Excel (or Apache OpenOffice or LibreOffice), and transpose is the mission critical factor in your use, then you should be using one of the tools that provides that feature.

     

    Really, it is that simple.

     

    Regards,

    Barry

  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Jan 15, 2014 5:56 AM (in response to Wayne Contello)

    I am a user, like you.  Numbers is simply a tool.  If it does not fit your needs, use the tool that does.  At this point it is free, and in some cases that is all it is worth.  I still like it 90% of the time.  The other 10% I use excel, or LibreOffice, or MatLab, or SciLab or DataGraph.

  • Alpha Centauri Calculating status...
    Currently Being Moderated
    Jan 29, 2014 2:46 AM (in response to cdevitarun)

    anyone tried the latest numbers update ? Give me a proper transpose and I will uninstall Excel and never look back.

  • Wayne Contello Level 6 Level 6 (12,790 points)
    Currently Being Moderated
    Jan 29, 2014 6:18 AM (in response to Alpha Centauri)

    If you are holding out for Transpose in Number I think you are making a mistake.  Transposition in Numbers, at this time, requires a temporary, second table.

     

    If your data is in a table called "Data" , then create a temporary table to perform the transposition.... let's call it "Temp".  You can eaven save this as a template so you can use when you need to perform this operation.

     

     

    In the table Table "Temp":

    Screen Shot 2014-01-29 at 8.15.24 AM.png

     

    A1=IFERROR(OFFSET(Data::$A$1, COLUMN()−1, ROW()−1), "")

    select A1, copy

    select all, paste

     

    you can resize the table "Temp" to match you Data table, then select the contents of the temp table and paste back to your original table using the menu item  "Edit > Paste Formual Results" (or <command> + <shift> + v)

  • Alpha Centauri Level 1 Level 1 (0 points)
    Currently Being Moderated
    Jan 29, 2014 5:25 PM (in response to Wayne Contello)

    Thanks Wayne, I will give it a shot tonight, it could save me opening XL just for that purpose, and when mobile, be useful if on a business trip and relying on the ipad.

     

    Much appreciated !

  • Alpha Centauri Level 1 Level 1 (0 points)
    Currently Being Moderated
    Jan 29, 2014 10:24 PM (in response to cdevitarun)

    Thanks Wayne, i made a spreadsheet that I can use to do what I call transposing a table. I have no idea why it works, but I copied your formula verbatim and voila, it worked.

     

    Is it possible to make a formula like the one you have, (iferror........row(etc)  and make it into a function stored in numbers ? This would save copying and pasting between spreadsheets.

  • SGIII Level 4 Level 4 (3,345 points)
    Currently Being Moderated
    Jan 30, 2014 8:43 PM (in response to Alpha Centauri)

    Give me a proper transpose and I will uninstall Excel and never look back.

     

    Ok, here's a proper transpose, that can be placed in an Automator Service so it becomes a simple menu pick as below (and can also be assigned a keyboard shortcut).

     

    Screen Shot 2014-01-30 at 11.12.32 PM.png

     

    To use it (this is slightly different from Excel) you select the range you want to transpose, choose Copy Transpose, click a destination cell in an existing table in the current document or another document, and command-v (Edit > Paste) or option-shift-command-v (Edit > Paste and Match Style).

     

    The one-time setup is as follows.  In Automator choose File > New > Service,  drag a Run AppleScript action from the left into the right pane, choose 'No Input' for 'Services receives selected' and 'Numbers' for 'in'. Then paste the following into the Run AppleScript action, replacing all text already there by default:

     

    --Transpose - select range, run, paste transposed values where wanted

    try

              tell application "Numbers" to tell front document to tell active sheet

                        set selected_table to first table whose class of selection range is range

                        tell selected_table

                                  set my_selection to the selection range

                                  set first_col to address of first column of my_selection

                                  set last_col to address of last column of my_selection

                                  set first_row to address of first row of my_selection

                                  set last_row to address of last row of my_selection

                                  set str to ""

                                  repeat with i from first_col to last_col

                                            repeat with j from first_row to last_row

                                                      set str to str & (value of cell j of column i of selected_table) & tab

                                            end repeat

                                            set str to str & return -- add line return after row

                                  end repeat

                        end tell

              end tell

      set the clipboard to str

              display notification "Ready to paste transposed values" with title "Numbers"

    on error

              display dialog "Select a range first and then try again"

    end try

    --end script

     

    Hit the compile "hammer" and the script should indent properly. Then save the service with the name you want to appear in your menu, and it will thereafter be available via the Services menu (and keyboard shortcut, if you set one up in System Preferences > Keyboard > Shortcuts > Services).

     

    That's it. Less then five minutes' one-time set-up work and you've got a menu pick for a transpose functionality that is as convenient as Excel's.

     

    SG

  • Jerrold Green1 Level 7 Level 7 (28,270 points)
    Currently Being Moderated
    Jan 31, 2014 7:04 AM (in response to SGIII)

    SG,

     

    That's a nice script. Very easy to use.

     

    Have you considered looking into being able to preserve formulas across the transposition? Yvan Koenig wrote such a script for Numbers V2, but as far as I can tell, it doesn't run in V3.

     

    Yvan's script is in his Box account if you'd like to take a look at it. Link

     

    Jerry

  • SGIII Level 4 Level 4 (3,345 points)
    Currently Being Moderated
    Jan 31, 2014 8:51 AM (in response to Jerrold Green1)

    Hi Jerry,

     

    Thanks for link to the Box account. Can see there is lots of interesting stuff in there. Unfortunately, I can't see or download the transpose script file (perhaps bandwidth limit exceeded?).

     

    Am curious to see how Ivan could have preserved formulas.  As far as I can tell there is no formula property for cells. You can use AppleScript to assign a formula to a cell by setting its value to "=" followed by the text of the formula. But you can't "read" a formula for insertion elsewhere, at least not easily.

     

    Meanwhile, for transposing values imported into Numbers, probably the most common use case, the script is working well here.

     

    As you've seen, all it does is read the value in each cell in each column of a selected range, placing a tab between values and a return after each column. The resulting tab-delimited string of data is then placed on the system clipboard so it can be pasted anywhere with command-v. Numbers happily accepts tab-delimited data pasted into an existing table, and even automatically expands the table as needed.

     

    SG

1 2 3 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.