transpose columns and rows in numbers

Hi Jerry,

Thanks for link to the Box account. Can see there is lots of interesting stuff in there. Unfortunately, I can't see or download the transpose script file (perhaps bandwidth limit exceeded?).

Am curious to see how Ivan could have preserved formulas. As far as I can tell there is no formula property for cells. You can use AppleScript to assign a formula to a cell by setting its value to "=" followed by the text of the formula. But you can't "read" a formula for insertion elsewhere, at least not easily.

Meanwhile, for transposing values imported into Numbers, probably the most common use case, the script is working well here.

As you've seen, all it does is read the value in each cell in each column of a selected range, placing a tab between values and a return after each column. The resulting tab-delimited string of data is then placed on the system clipboard so it can be pasted anywhere with command-v. Numbers happily accepts tab-delimited data pasted into an existing table, and even automatically expands the table as needed.

SG

Your formula works quite well for values. Handling formulas is much more involved because not only do you need to put the formula in the proper place, you must convert all the references.

The Transpose script is about a third to half way down that page I linked to. The file name is, get this, Transpose.zip. 🙂

Jerry

No problem figuring out which one it is. But get no response when clicking download, even after opening a Box account and trying again to download after signing in.

I'd say my script works "very" (not "quite") well for transposing values.🙂 I'm surprised how well it worked out. It's much shorter and simpler than the scripts I've been able to locate on in past threads. Suitable for an Automator Service.

SG

This is frustrating, SG. I tried this and it worked a treat. Saved the service, it appears in the services menu, tried the transpose and it worked, although integer data was returned in a strange format.

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

so far this is the best solution I've seen I just wish I coud get it working.

SGIII wrote:

No problem figuring out which one it is. But get no response when clicking download, even after opening a Box account and trying again to download after signing in.

I'd say my script works "very" (not "quite") well for transposing values.🙂 I'm surprised how well it worked out. It's much shorter and simpler than the scripts I've been able to locate on in past threads. Suitable for an Automator Service.

SG

Actually, I find your script to be supercalifragilistic. Beyond words, and without equal.

If you shoot me an email, I'll return a copy of Yvan's script to you.

Jerry

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 SG

I tried it again and this time it's worked and is persistent!

This is an absolutely brilliant script and I can't thank you enough for it. It may even be enough to stop me b1tching about Apple, at least until they release an update and break it. I've created a keyboard shortcut as well and it's actually now even simpler to transpose than it ever was in Excel. I've tried every solution that's been posted on every board and this is the only one which has worked consistently and without undue fuss and complication. It's not that I object to using Excel, I just object to paying for MS Office just to get the transpose function when I would use nothing else in the whole suite, and because I have enough background apps running at any one time I don't even like the idea of launching a free suite like Apache Office just to use one command...

Mission accomplished. I can only assume that SG stands for Scripting Guru.

Re: your last comment; I think that was the problem. I had been comparing the autosum/autoaverage function in the previous version of Numbers to that in the new version which is markedly inferior. In the new version you select a cell at the bottom of a vertical range, click the fx button and select sum. The problem is that it only references cells above the selected one so it won't sum or average a horizontal array. This has completely screwed my accounting as I compile my turnover under monthly headings, sum the daily takings at the bottom of each column then select the horizontal array containing these values and sum them to the right adding daily averages to the right using all the data in the arrays above and producing a monthly average from these for projection purposes. This was a breeze in the older version of Numbers where you selected an array of cells you wanted to sum or average and it returned the result at the end of the array, but I can't do it in the new one, and I can't see a workaround... 😟

Eltham Jones wrote:

... I had been comparing the autosum/autoaverage function in the previous version of Numbers to that in the new version which is markedly inferior. In the new version you select a cell at the bottom of a vertical range, click the fx button and select sum. The problem is that it only references cells above the selected one so it won't sum or average a horizontal array. This has completely screwed my accounting as I compile my turnover under monthly headings, sum the daily takings at the bottom of each column then select the horizontal array containing these values and sum them to the right adding daily averages to the right using all the data in the arrays above and producing a monthly average from these for projection purposes. This was a breeze in the older version of Numbers where you selected an array of cells you wanted to sum or average and it returned the result at the end of the array, but I can't do it in the new one, and I can't see a workaround... 😟

Eltham,

Select the range of cells that you want to SUM and then examine at the bottom of your Numbers window. You will find the SUM and other statistics there, with the option to add others (gear icon on right). Drag the stat you want to the cell where you want to display it.

Jerry

Ah, the bit that's always hidden by the dock...

Sneaky, Thanks Jerry, my wishlist is sorted!

"Ah, the bit that's always hidden by the dock..."

End the cover-up!

System Preferences > Dock:

Regards,

Barry

I know... Normally it is hidden but if I have a lot of background apps running, on my older machine there is a lot of latency and the dock can be "sticky" so I leave it out. Sometimes you have to compromise!

I've tried every solution that's been posted on every board and this is the only one which has worked consistently and without undue fuss and complication.

Glad it's working there. It's also solved the problem for me. I like simple with no fuss too, especially when I have such a limited repertoire. The pros who look at it will see the signs of an amateur. But it works. And I figure that's what AppleScript (and indeed Numbers) are for: they put a lot of computing power into the hands of non techies.

@Jerry, thanks for the link to Ivan's script. It finally came alive for me today. (Maybe a new month and a new bandwidth quota?) Anyway, I'll see if I can follow along (it looks complicated!) and maybe update it to work with Numbers 3.

SG

HI SG,

Yvan is quite sensitive about the spelling of his name, and isn't here to defend it. Please try to get it right.

Regards,

Barry

EJ,

"Sometimes you have to compromise!"

Understood. One does what one thinks is best.

Regards,

Barry

HI SG,

Yvan is quite sensitive about the spelling of his name, and isn't here to defend it. Please try to get it right.

Regards,

Barry

Yes, Larry🙂. My apologies to Yvan, though. I hope my mental lapse hasn't detracted from his contributions to the Numbers user community. I'm sensitive about names myself, being (apparently) the only living person on the planet with my particular combination of just four common English syllables. The problem is that they could easily form two first names or two last names. Most decide the first must be the last. Or the last must be the first. Some throw in a tiny spelling "correction" to confirm whatever bias they may have toward the natural order. Anyway, I'll watch my i grec.

SG