transpose columns and rows in numbers

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

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.

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

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.

SG,

Is there a way to make this script cut rather than copy?

Also is there a way to limit how many columns it transposes in? i.e. I have a table where all the values are in column 1. I need to transpose every 3 values into separate rows. Is there a way to do this without having to copy paste every time?

A1 To A1 A2 A3

A2 B1 B2 B3

A3

B1

B2

B3

Thanks

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

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.

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.

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

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.

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)

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 !

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.

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

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