transpose columns and rows in numbers

Still don't understand why you think you need "temporary tables." If you've set things up properly, the horizontally arranged values in your input table are placed automatically by INDEX(TRANSPOSE) in vertically arranged cells in your output ("master dataframe"??) table. It's very hard to tell from your screenshots what your actual setup is. But in general, time spent setting things up properly so you don't need workarounds and temporary tables and such is time well spent.

SG

Ok, let me describe my setup. I've got several sheets within a single file, for the different regions of the hand (wrist bones, palm bones, finger bones), with a right and a left sheet for each region. These are arranged for taking data, i.e., with a single bone occupying three rows: two for entering measurements, and a third with a formula for finding the average of the two scores above. Then, I have a separate master sheet, in a data frame format, rather than a table. Instead of each bone having its own row with multiple measurements, each measurement gets its own row, with identifying information preceding it (i.e., genus and species name, left or right, bone name, measurement taken, etc.). This is the format that it needs to be set up in for R to read, so that I can export as a CSV and actually use the data in analyses.

I've tried pasting the INDEX(TRANSPOSE) formula into the master sheet, but I haven't figured out how to not get a "data outside of valid range" type message, like in the photos above. The only way I've managed to make this work, without getting the red triangle error, is to put the formula into a temporary table with the same (inverted) dimensions of the input row. So, unless I'm missing something (which is still possible), I'll need to create a new sheet, full of temporary tables of the proper dimensions that I can put the index formula in, then tell the master sheet to look at the temporary tables for the data, so that I'll be able to drag down the yellow circle, since the transposed data will be in column format to begin with.

This does seem cumbersome, but I just couldn't figure out how to get the formula to work in the master sheet. I could still be missing something.

It's a little hard to follow your description, partly because of nomenclature issues. Numbers has "documents", and within documents it has "sheets", and on each sheet (marked by tabs at the top) it has "tables." Formulas and values can be in tables (not sheets). You should be able to have one formula in your output table that places in one column values that are horizontal in the input table.

A picture is worth a thousand words.

SG

Hi Andy,

Numbers is well suited to creating a table that is similar to a dataframe in R. Column headings in row 1 and data for each attribute down each column. I am not sure that R will recognise the file (document) format of Numbers. Also, your raw data tables and intermediate calculations (transpose and average) make for a complex document. Aim for a simple final table.

When your final table is ready, select the whole table and Copy.

Open a new blank document in TextEdit and Paste. Save.

That will create a tab-delimited plain text file that R will recognise.

Regards,

Ian.

"Posting feedback to Apple is a waste of time Wayne."

I would have to disagree on this point. Many things we as users asked for have shown up, they do read the suggestions and implement many. We didnt have Hide columns on ipad forever, now we do as one obvious example i wanted forever. It just takes enough of us to go there and make the suggestion. Its just like voting, if you dont vote/suggest you cant really complain. Yours could be the one vote/suggestion that put that above another in the queue of things to include in the next updates.

Jason

I too often need a transpose function to rearrange data supplied by outside vendors, e.g. Gas & Electric Company. Darned if I didn't find it in Numbers!!!

Using Numbers version 3.5, select your dataset, then go to: Table>Transpose Rows & Columns.

This turned my whole "vertical" table into a "horizontal" table, and I then had to cut/paste the columns I wanted to stack to get my final result, but this was far, far easier for my simple brain than using INDEX and TRANSPOSE functions.

Yep, after several years of ignoring all the Mac users who were telling them "YES! we do want a transpose function" and Apple making lame excuses because they stupidly forgot to include it (but will never admit to the fact that they got something wrong), they have finally given us the function that we have been asking for all these years.

And guess what? Now it's a "Great New Feature!"... 😁

+1

Thanks for the positive feedback! Although Numbers now has the menu pick to transpose an entire table, the Automator Service with script is still handy to transpose a range of cells within a table.

SG

Rau Arbeitsschutz wrote:

still using numbers (2.0.3) and your transpose script.

So is it possible with you script to transpose the formular of the cells and not the values. For example: "='Tabelle 1-1-1' :: B2"

Hi Rau,

It is possible. I understand one of Yvan Koenig's scripts does this in Numbers 2. But it is too complicated for me. Recommend moving to Numbers 3.

SG

Hi Duncan,

This is an old thread about a 7-year-old version of Numbers no longer under development.

In the current version of Numbers all you have to do is click in the table and choose 'Transpose Rows and Columns' from the Table menu.

SG

Here's the easiest way yet - just do it on a google spreadsheet.

As long as you have a google or gmail account this works.

Go to docs.google.com. Click the menu button and open a new spreadsheet.

Right click on a cell. Paste special -> paste transpose

that will work, now copy and paste back into numbers.

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

"Here's the easiest way yet - just do it on a google spreadsheet."

How is copying, pasting special to a Google spreadsheet, copying again from there and pasting into Numbers easier than the procedure below, posted by SG March 3rd, is response to Duncan Parks?

"In the current version of Numbers all you have to do is click in the table and choose 'Transpose Rows and Columns' from the Table menu.

SG"

EDIT: ...and repeated immediately above.

Regards,

Barry

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

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 !