How to Transpose a table? (Using TRANSPOSE)

Here is a link to a previous post in this forums where I address how to transpose using formulas and BadUnit addresses the same problem using Applescript:

https://discussions.apple.com/thread/4114507?answerId=18944720022#18944720022

the functions:

row()

and column() provide a number indicating the row or column the formula is in. these are useful for providing information to the offset function

The table on the left contains the original data (and is shrewdly named... "Original Data") and the table on the right is where the transposed data appears.

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

this is shorthand for... select cell A1 in the the table on the right , then type (or copy and paste from here) the formula:

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

select cell A1, copy

select all cells in the table on the right by selecting any cell, then use the menu item "Edit > Select All"

paste

If you want the resuls of the trasposed table, select all, then copy, then select the destination and select the menu item "Edit > Paste Formula Results"

Hi,

I find this Copy Transpose Automator Service (Dropbox download) convenient on my machine:

I just select the cells I want to transpose and choose Copy Transpose from the Numbers > Services menu. Then I single-click a cell where I want the transposed values to appear, and type command-v to paste. That's it. No need to set up tables and formulas and all that each time you want to transpose.

To install, just double-click the .workflow package (and, if necessary, click 'Open anyway' in System Preferences > Security & Privacy). The service should work with both Numbers 2 and Numbers 3, though only one version of Numbers should be open when you run it.

SG

P.S. Below for reference is the AppleScript that is in the Automator Service. You don't need to do anything with it to install the service; just double-click. (You can rename or delete the service by holding down the option key in Finder, choosing Library, and navigating to Services, where you can rename or delete like any other item in Finder).

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

--SGIII 2014.05,for v2 and 3, https://discussions.apple.com/thread/6237667?tstart=0

try

tell application "Numbers" to tell front document to tell (first sheet whose every table's selection range's class contains range)

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

tell selected_table

tell the selection range

set first_col to address of its first column

set last_col to address of its last column

set first_row to address of its first row

set last_row to address of its last row

end tell

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 of script

did you review my post? I used the offset function to swap the row/columns into a new table.

The Service took two rows at a time and pasted them all into THE SAME two columns; the script only transposed the first row and two cells of the next!

Weird.

That is weird. I am not quite sure how it is possible to make what you describe happen. The service can't paste anything; you have to paste. I retested here, starting from downloading from the link above, and the service works as expected.

To review, the steps are:

1. Doubleclick the .workflow package (and if necessary click 'Open Anyway' in System Preferences > Securty & Privacy). This one-time step installs the service in your Numbers > Services menu.
2. In Numbers (2 or 3) select the cells that you want to transpose.
3. Choose 'Copy Transpose' from the Numbers > Services menu, and wait for the notification.
4. Click a destination cell once and command-v (or Edit > Paste and Match Style) to paste. Numbers will expand the table automatically if needed.

That's it. No setting up tables, making sure they are the correct dimensions, and entering OFFSET formulas each time you want to do this. Just select cells to transpose, choose a menu item, and paste the results where wanted. Very similar to Excel (though the order of the steps is slightly different).

SG

So, I'm not up to speed with how these scripts/automator works

But the first time i downloaded your zip file and installed it and ran it in automator it worked fine in numbers.

Then when I tried to repeat that operation services-->etc it didn't work and it didn't show that the script was running.

How do I make it so this works all the time?

Thank you

Does it show up in your Numbers > Services menu?

If so, the steps are:

1. Select the range of cells that you want to transpose.
2. With the cells selected, choose Copy Transpose from the Numbers > Services menu.
3. When the notification appears (usually within a second or two) click once on a cell in a table where you want the transposed values to appear, and type command-v to paste.

That's all there is to it.

If you're not getting any results, try quitting Numbers, shutting down your machine, and restarting.

SG

it worked!!!! thanks🙂