Q: transpose columns and rows in numbers

Glad it worked.  Thanks for the positive feedback!

SG

just out of curiosity, any idea why the variable assignment doesn't seem to work in Numbers 2 on mountain lion?

It compiled just fine and was finding the selection range and telling me what the range was (I added some display dialog lines to try to find where the error was occurring and used the name property to see if it was getting the selection and it could do "name of selection range" but not "name of my_selection" so I figured the assignment of selection range to my_selection wasn't working in Numbers 2/mountain lion. But it's an odd error since assignments should be pretty foolproof.  

Not a big deal really since it works now.

I'm definitely in the "child" category by the way. I used http://macosxautomation.com/applescript/iwork/numbers/range.html to give me a little bit of a clue but I don't actually know what I'm doing.

Thanks again.

(sorry, I'm Thor Klamet2 now, it made me create another account for some reason to use the support forum again, no idea why, same person as before though)

Perhaps you could post here the script that doesn't work, and I'll try to troubleshoot it with Numbers 2.  The best way to post a script now seems to be to click 'Use advanced editor' (upper right), the click the >> Syntax Highlighting > Plain.

SG

That is marvellous. Thanks very much for saving me hours of work

Great to hear it is helping others.  Thanks for the feedback!

SG

Hi SG,

Now that you are on Level 5 (Bravo! for all that work helping others) how about posting a User Tip "The easy way to AppleScript" .

Regards,

Ian.

Hi Ian,

Ah, User Tips seem to be easily accessible again. You posted a nice one today.

With the new ASC format, I still haven't figured out how to post AppleScript with proper indentation.

I'm still feeling a little lost here, but glad to see the helpful leaders are still here!

SG

I got the same error the first time I tried it. I looked back at what I had copied and pasted into Automator, and found that I needed to delete the header and footer that SGIII had included: "--Transpose - select range, run, paste transposed values where wanted" & "--end of script." Once I did that, worked like a charm.

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!

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

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

Oh, and SG, just the values from a given cell would be fine. It doesn't need to be a formula. The output from the formula is what I'm after.

Basically, my problem is that when I tell a cell the address of another, on a separate sheet, then drag down to fill a column, the address fills in as a column, rather than as a row. I need it to transpose the addresses so that the row addresses fill in on the column. I hope this makes sense...

I think the problem is that you're missing the $ anchors.  $7:$7, not 7:7. Note I have $4$4 in my example.  You can set the anchors by checking the Preserve Row after clicking the disclosure triangle in the address token.

SG