transpose columns and rows in numbers

Just select the table you want to transpose and click on the menu bar item: "Table > Transpose Rows and Columns"

Transpose Rows and Columns was added years ago.

SG

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

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).

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 tostr

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

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.

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.

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"

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.

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":

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)

Hi Eltham Jones,

tried the transpose and it worked, although integer data was returned in a strange format.

Try formatting the cells as number in the source area rather than leaving them as Automatic and see if that helps.

As soon as I quit Automator, it stopped working. i tried recompiling in Automator but despite doing everything I did before I get an error message "Expected end of line but found class name".

I can't reproduce that problem here. Things to doublecheck:

Try copying the script from the post above and pasting the script into Automator again. You might inadvertently have made an invisible change before you saved it. I just tried copying from the post above and pasting it into Automator and it compiled without error here. When selecting the script above to copy, there's no harm in including the line above and the line below preceded by --. But make sure you get them both. -- ok. Only - not ok.

Make sure the Run AppleScript action is set to 'no input' and in 'Numbers.app':

Make sure you deleted all of this default stuff in the Run AppleScript action. The script should replace everything there, not just the (* Your script goes here *) part.

And finally, quit Numbers 2.3 when you run this. It works on Numbers 3.1 and AppleScript can get confused if the old Numbers is open at the same time.

SG

Hi SGIII,

Count me among the happy users of your AppleScript! Thank you!

I've got another question for you, though. Perhaps this belongs in a new thread, but it seems like a logical step in this one. I am creating a data frame that I can analyze in R. However, I created a table while I was collecting my data, into which I entered two measurements of a given characteristic, then below those two measurements, I inserted an "Average" function, so that the two measurements would be averaged. The "Average" row is slightly darker, below:

That works fine for recording data, but is not great for a dataframe, in which all of the data are arranged in columns, instead of rows. Needing to transpose the values led me to this thread, where I ran your AppleScript, which worked great.

However, I am now trying to create a Template so that, in the future, when I record data in the above table, the averages will automatically be entered into the dataframe format, rather than having to be manually transposed, one row at a time. Here's a screenshot from the dataframe, of the same range of values:

Your script works fine for values, but not for formulae. For instance, when I copy a formula location from the first table above into the dataframe, it will copy, but then dragging down the yellow circle on the lower edge of the cell to copy corresponding locations onto the cells below will not transfer row locations, only column ones. Would it be possible to write an AppleScript that would allow me to transpose the locations of a row into a column, so that the results of the averages in the above table would automatically be entered into the dataframe? It's possible to do this by typing in the address of each cell manually, but I'm working with thousands of measurements and being able to simply copy and paste via an AppleScript would make this much easier!

Thanks again for your help with transposing, and thanks very much in advance for your help on this!

I am now trying to create a Template so that, in the future, when I record data in the above table, the averages will automatically be entered into the dataframe format, rather than having to be manually transposed

Here is one way to do what you describe, using a formula instead of a script:

The formula in D2, copied down, is:

=INDEX(TRANSPOSE(Table 1::$4:$4),ROW()−1,1)

SG

SG & t quinn,

I've tried both of your suggestions and this is what I get, one picture showing the output (error) in the dataframe that I want the data to transfer to and the other showing the original table and selection.

Is there a problem with the formula that I have entered? I can't get it to return a number in the dataframe (I'm hoping for 16.38, then 19.44 below it, then 9.185, and so on).

Thanks for your help,

andy

beckminsterfullerene wrote:

(Also BTW I tried to do that "services" thing to add the function to Numbers, but it didnt work.)

Well, that "service" thing worked well while it was needed. But this is a REALLY old thread. In Numbers 3 it's just a simple menu pick.

SG

Do you need a "live" formula to be transposed or just the results of the formula?

With recent enhancements to Numbers AppleScript support it is once again possible to transpose "live" formulas. But that's beyond my coding skills, and you might want to post the question on the Mac OS X Technologies discussion.

If it's just the result of the formula that you need in a transposed position, then the problem is much simpler and I can probably provide a solution. Let me know.

SG

Hi andy,

You may not want a script at all.

Take another look at Jerry's suggestion from page 1:

Jerrold Green1

You may need to add or subtract to your column or row functions to get things to work out. No worries about moving functions they can stay where they are.

I have used OFFSET(base, row-offset, column-offset, rows, columns) a lot in the past where COLUMN()-x is the row-offset and ROW()-y is the column offset. INDEX seems cleaner.

quinn