Q: Using the new Transpose function

It seems that you failed to read the Formulas and Functions User Guide which describes perfectly the way to use this function (pages 209 & 210 in English version).

TRANSPOSE
+The transpose function returns a vertical range of cells as a horizontal range of cells, or vice versa.+
TRANSPOSE(range-array)
+• range-array: The collection containing the values to be transposed. range-array is a collection containing any type of value.+
+Usage Notes+
+• TRANSPOSE returns an array containing the transposed values. This array will+
+contain a number of rows equal to the number of columns in the original range and a number of columns equal to the number of rows in the original range. _The values in this array can be determined (“read”) using the INDEX function._ +

Examples
+Given the following table:+
+row/column A B C D E+
+1 5 15 10 9 7+
+2 11 96 29 11 23+
+3 37 56 23 1 12+

+=INDEX(TRANSPOSE($A$1:$E$3),1,1) returns 5, the value in row 1. column 1 of the transposed range+
+(was row 1, column A, of the original array).+
+=INDEX(TRANSPOSE($A$1:$E$3),1,2) returns 11, the value in row 1, column 2 of the transposed range+
+(was row 2, column A, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),1,3) returns 37, the value in row 1, column 3 of the transposed range+
+(was row 3, column A, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),2,1 returns 15, the value in row 2, column 1 of the transposed range+
+(was row 1, column 2, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),3,2) returns 29, the value in row 3, column 2 of the transposed range+
+(was row 2, column C, of the original range).+
+=INDEX(TRANSPOSE($A$1:$E$3),4,3) returns 1, the value in row 4, column 3 of the transposed range+
+(was row 3, column D, of the original range).+

Yvan KOENIG (from FRANCE mardi 13 janvier 2009 10:26:36)

No, he read it fine. Read his question again, as it's something that's been puzzling me.

Is there a way to transpose a section of a table? Specifically, can I move A1:B5, say, so that the same data occupies the cells A10:E11. Same shape, but transposed.

I see the new TRANSPOSE function, I've read how to use it, but I still can't see how to simply transpose a section of a table.

Martin

I didn't use a special function in one cell. I used one equation that just swapped out the Row and column from another table, using address and index.

I think we posted this version about a year (possibly more) ago.

Jason

I really don't understand where is the problem.
Just do what is in the help.



F2 =INDEX(TRANSPOSE($B$3:$D$6),1,1)
F3 =INDEX(TRANSPOSE($B$3:$D$6),1,2)
F4 =INDEX(TRANSPOSE($B$3:$D$6),1,3)
F5 =INDEX(TRANSPOSE($B$3:$D$6),1,4)

G2 =INDEX(TRANSPOSE($B$3:$D$6),2,1)
G3 =INDEX(TRANSPOSE($B$3:$D$6),2,2)
G4 =INDEX(TRANSPOSE($B$3:$D$6),2,3)
G5 =INDEX(TRANSPOSE($B$3:$D$6),2,4)

H2 =INDEX(TRANSPOSE($B$3:$D$6),3,1)
H3 =INDEX(TRANSPOSE($B$3:$D$6),3,2)
H4 =INDEX(TRANSPOSE($B$3:$D$6),3,3)
H5 =INDEX(TRANSPOSE($B$3:$D$6),3,4)

As I am lazy, in F2 I typed:
=INDEX(TRANSPOSE($B$3:$D$6),ROW()-ROW(A$1),COLUMN()-COLUMN($E1))
then fill down from F2 thru H2
then fill to right thru H5


Sure, it's not what I wanted for a TRANSPOSE() function.

My hope was that the formula
F2 =TRANSPOSE($B$3,$D$6)
would be able to AUTOMATICALLY fill the block F2…I4

But the Help is clear, it's not what is available.

Yvan KOENIG (from FRANCE mardi 13 janvier 2009 14:58:57)

But that's horrendous! It's quicker to type in all the values from scratch.

Martin

As an end user like you, I'm just able to describe the way the function must be used. It's exactly what is described in the Help.
If you didn't understood, it's not my fault.

Of course, I posted a report to Bugs Hunters:

+Your tracking number for this issue is Bug ID# 6491815.+

+In the the thread:+
+http://discussions.apple.com/thread.jspa?messageID=8799221+
+of the Discussions forum dedicated to Numbers '09 I posted:+


+F2 =INDEX(TRANSPOSE($B$3:$D$6),1,1)+
+F3 =INDEX(TRANSPOSE($B$3:$D$6),1,2)+
+F4 =INDEX(TRANSPOSE($B$3:$D$6),1,3)+
+F5 =INDEX(TRANSPOSE($B$3:$D$6),1,4)+

+G2 =INDEX(TRANSPOSE($B$3:$D$6),2,1)+
+G3 =INDEX(TRANSPOSE($B$3:$D$6),2,2)+
+G4 =INDEX(TRANSPOSE($B$3:$D$6),2,3)+
+G5 =INDEX(TRANSPOSE($B$3:$D$6),2,4)+

+H2 =INDEX(TRANSPOSE($B$3:$D$6),3,1)+
+H3 =INDEX(TRANSPOSE($B$3:$D$6),3,2)+
+H4 =INDEX(TRANSPOSE($B$3:$D$6),3,3)+
+H5 =INDEX(TRANSPOSE($B$3:$D$6),3,4)+

+As I am lazy, in F2 I typed:+
+=INDEX(TRANSPOSE($B$3:$D$6),ROW()-ROW(A$1),COLUMN()-COLUMN($E1))+
+then fill down from F2 thru H2+
+then fill to right thru H5+

+Sure, it's not what I wanted for a TRANSPOSE() function.+

*+My hope was that the formula+*
*+F2 =TRANSPOSE($B$3,$D$6)+*
*+would be able to AUTOMATICALLY fill the block F2…I4+*

+But the Help is clear, it's not what is available.+

*+Was it really necessary to build the TRANSPOSE() function this way ?+*
*+I'm quite sure that my described hope was perfectly doable … and more efficient.+*

Yes, in the report as in the forum, I forgot the formulas for row I
With my single formula it's really quick.
I can't do more.
Now, wait and see.

Yvan KOENIG (from FRANCE mardi 13 janvier 2009 15:49:31)

What are you waiting from helpers in this forum?

We are end users like you.
We can't change the program's behavior.
You asked how the function may be used.
I gave the exact nd complete response.
You don't like it.
I may understand but I repeat I am not an Apple engineer.


I already wrote:

*Sure, it's not what I wanted for a TRANSPOSE() function.*

*My hope was that the formula*
*F2 =TRANSPOSE($B$3,$D$6)*
*would be able to AUTOMATICALLY fill the block F2…I4*

I posted a report to Bugs Hunters.
It's the maximum of what I may do.

If you aren't satisfied, don't rant about a user like me,
_Go to "Provide Numbers Feedback" in the "Numbers" menu_, describe what you wish.
Then, cross your fingers, and wait maybe until iWork'10

Yvan KOENIG (from FRANCE mardi 13 janvier 2009 17:35:08)

no, formulas aren't horendous. I can type one (1) equation and just fill it in for the range, and I have done hundreds of cells in a matter of seconds. Performing our "transpose" accurately.

I know, I have done it.

Jason

You don't have to rely upon the Transpose function if you don't like it, I just put values in the number 1 row and used this funciton to transpose them from cell A3 down.

=INDIRECT(ADDRESS(1,ROW()-2))

I typed it in less than ten seconds, and filled it down in less for many rows. Simple. We are only here to give our oppinions as to how to get the program to do as you ask.

many times it really is this simple, and people just make it harder than it needs to be. Yvan gave you an answer based upon the function you asked about, It may not be as pretty as you like, but it worked.

Transpose doesn't work as you expect it because the engineers decided not to implement excel style Array formulas that very few people understand. And even fewer end up using.

Just my 2 cents,
Jason

Hello

Here is a 'transpose' script.

--[SCRIPT transpose]

(*
Thanks to Scott Lindsey & Ed.Stockly from applescript-users@lists.apple.com

Copy a group of cells in the clipboard.
Put the cursor where you want.
Run the script.
It will insert the transposed set of values.
Of course, it will be a 'frozen' block.
Changes in the source one will not be reflected.

Yvan KOENIG (Vallauris, FRANCE)
15 janvier 2009
*)

on run
 try
 set avant to the clipboard as text
 on error
 error "No copied values in the clipboard !"
 end try
 set avant to paragraphs of avant
 
 repeat with i from 1 to count of avant
 set item i of avant to my decoupe(item i of avant, tab)
 end repeat
 
 set {rName, tName, sName, dName} to my getSelection()
 if rName is missing value then error "No selected cells"
 
 set twoNames to my decoupe(rName, ":")
 set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
 (*
if item 2 of twoNames = item 1 of twoNames then
 set {colNum2, rowNum2} to {colNum1, rowNum1}
else
 set {colNum2, rowNum2} to my decipher(item 2 of twoNames)
end if
*)
 (* Here we know the starting point of the destination area. *)
 
 tell application "Numbers"
 activate
 tell document dName to tell sheet sName to tell table tName
 
 set rowsCible to (get row count)
 set rowsNeeded to rowsCible - 1 + (count of item 1 of avant)
 if rowsNeeded > rowsCible then
 repeat (rowsNeeded - rowsCible) times
 add row below row rowsCible
 end repeat
 end if -- rowsNeeded
 
 set columnsCible to (get column count)
 set columnsNeeded to columnsCible - 1 + (count of item 1 of avant)
 if columnsNeeded > columnsCible then
 repeat (columnsNeeded - columnsCible) times
 add column after column columnsCible
 end repeat
 end if -- columnsNeeded
 
 repeat with i from 1 to count of avant
 repeat with j from 1 to count of item 1 of avant
 set value of cell (colNum1 - 1 + i) of row (rowNum1 - 1 + j) to item j of item i of avant
 end repeat
 end repeat
 end tell
 end tell
end run

--=====

on getSelection()
 local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
 --tell application "Macintosh HD:Applications:iWork '09:Numbers.app"
 tell application "Numbers"
 activate
 tell document 1
 set mySelectedRanges to selection range of every table of every sheet whose it is not missing value
 repeat with sheetRanges in mySelectedRanges
 try
 count of sheetRanges
 on error
 set sheetRanges to {sheetRanges}
 end try
 repeat with thisRange in sheetRanges
 if contents of thisRange is not missing value then
 try
 --return thisRange --poorly formed result
 thisRange as text
 on error errMsg number errNum
 set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)
 --set mySelection to (a reference to (range rn of table tn of sheet sn))
 return {myRange, myTable, mySheet, myDoc}
 end try
 end if -- contents…
 end repeat -- thisRange
 end repeat -- sheetRanges
 end tell -- document 1
 end tell -- application
 
 return {missing value, missing value, missing value, missing value}
end getSelection

--=====

on decipher(n)
 local letters, colNum, rowNum
 set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 if (character 2 of n) as text > "9" then
 set colNum to (offset of (character 1 of n) in letters) * 64 + (offset of (character 2 of n) in letters)
 set rowNum to (text 3 thru -1 of n) as integer
 else
 set colNum to offset of (character 1 of n) in letters
 set rowNum to (text 2 thru -1 of n) as integer
 end if
 return {colNum, rowNum}
end decipher

--=====

on decoupe(t, d)
 local l
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 return l
end decoupe

--=====
--[/SCRIPT]

I wish to add that the "old" formula given by jaxjason is more efficient than what we may do with the new TRANSPOSE() function.

Yvan KOENIG (from FRANCE jeudi 15 janvier 2009 12:33:00)

Apologies for this possibly ignorant question, but I don't understand how to do this:

"As I am lazy, in F2 I typed:
=INDEX(TRANSPOSE($B$3:$D$6),ROW()-ROW(A$1),COLUMN()-COLUMN($E1))
then fill down from F2 thru H2
then fill to right thru H5
Sure, it's not what I wanted for a TRANSPOSE() function."

I understand first half of the syntax, but what does "ROW()-ROW(A$1),COLUMN()-COLUMN($E1))" refer to? I typed the exact equation above for a similar looking data array, but I get the error "this formula contains an invalid reference." What is supposed to be in the empty parenthesis?

I have a data set with several hundred rows and dozens of columns, so I don't have the time to type the formula into each cell and would like to just fill down and across. Thanks in advance, and apologies for the elementary inquiry...I just started using numbers today!

I apologizes but my formula behaves perfectly.
Of course it assumes that you _are running Numbers in English_ in a country where the _decimal separator is the period._
If you use Numbers in English in a country where the decimal separator is comma, you must replace commas by semi-colons in the formulas.

I wrote:

F2 =INDEX(TRANSPOSE($B$3:$D$6),1,1)
F3 =INDEX(TRANSPOSE($B$3:$D$6),1,2)
F4 =INDEX(TRANSPOSE($B$3:$D$6),1,3)
F5 =INDEX(TRANSPOSE($B$3:$D$6),1,4)

G2 =INDEX(TRANSPOSE($B$3:$D$6),2,1)
G3 =INDEX(TRANSPOSE($B$3:$D$6),2,2)
G4 =INDEX(TRANSPOSE($B$3:$D$6),2,3)
G5 =INDEX(TRANSPOSE($B$3:$D$6),2,4)

H2 =INDEX(TRANSPOSE($B$3:$D$6),3,1)
H3 =INDEX(TRANSPOSE($B$3:$D$6),3,2)
H4 =INDEX(TRANSPOSE($B$3:$D$6),3,3)
H5 =INDEX(TRANSPOSE($B$3:$D$6),3,4)

which are the formulas applying exactly what the Help describes.
In my lazy formula,
ROW()-ROW(A$1) is used to calculate the first parameter
COLUMN()-COLUMN($E1) is used to calculate the second parameter
in F2 the first parameter will be 1, the second will be 1
in F3 the first parameter will be 1, the second will be 2
…
in G2 the first parameter will be 2, the second will be 1
in G3 the first parameter will be 2, the second will be 2
…

As I wrote, it's a lazy one because it doesn't apply if the 1st cell of the transposed block is not F2

Here is an enhanced one

=INDEX(TRANSPOSE($B$3:$D$6),ROW()+1-ROW($F$2),COLUMN()+1-COLUMN($F$2))
It works if the first cell is F2

If you want to get the transposed block starting in K12, just edit the formula this way:
=INDEX(TRANSPOSE($B$3:$D$6),ROW()+1-ROW($K$12),COLUMN()+1-COLUMN($K$12))

Before the arrival of TRANSPOSE(), I used OFFSET() which, from my point of view, is easier to apply.

=OFFSET($B$3,COLUMN()-COLUMN($F$16),ROW()-ROW($F$16))
does the trick if the transposed block starts in $F$16.

Last not least, we may use my script entitled: transposeLive.

I post it here for the last time because it is available on my iDisk:
<http://idisk.me.com/koenigyvan-Public?view=web>

download:
For_iWork:iWork '09:for_Numbers09:Transpose.zip

--[SCRIPT transposeLive]

(*

Enregistrer le script en tant qu'Application ou Progiciel : transposeLive].app
déplacer l'application créée dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Merci à Scott Lindsey & Ed.Stockly du forum applescript-users@lists.apple.com
qui m'ont aidé à construire le code récupérant le bloc sélectionné.

Sélectionnez le bloc de cellules à transposer
menu Scripts > Numbers > transposeLive (exécute le script une première fois)
Le script récupère les informations utiles sur le bloc à transposer

Sélectionner la première cellule du bloc destination
(elle ne peut être dans le bloc source)
menu Scripts > Numbers > transposeLive (exécute le script une deuxième fois)
Il insère des formules récupérant le contenu du bloc transposé.
Les modifications au contenu du bloc source seront répercutées.

+++++++++

Save the script as an Application or an Application Bundle: transposeLive].app

Move the newly created application into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

Thanks to Scott Lindsey & Ed.Stockly from applescript-users@lists.apple.com
which helped me to build the code grabbing the selected range.

Select a group of cells.
menu Scripts > Numbers > transposeLive (Run the script once.)
It grabs infos about the selection

select the first cell of the destination
(can't be in the source range)
menu Scripts > Numbers > transposeLive (Run the script one more time.
It will insert formulas grabbing the transposed range.
Changes in the source one will be reflected.

Yvan KOENIG (Vallauris, FRANCE)
19 janvier 2009
*)

property theApp : "Numbers"
property sourceCell : missing value
property sourceTable : missing value
property sourceSheet : missing value
property nbRows : missing value
property nbColumns : missing value

--=====

on run
 set firstPass to sourceCell = missing value
 
 if firstPass then (*
 Here we are in the first pass.
 We grab infos about the source range *)
 set {rName, sourceTable, sourceSheet, dName} to my getSelection()
 if rName is missing value then error "No selected cells"
 
 set twoNames to my decoupe(rName, ":")
 if sourceTable does not start with "'" then set sourceTable to "'" & sourceTable & "'"
 if sourceSheet does not start with "'" then set sourceSheet to "'" & sourceSheet & "'"
 
 set sourceCell to item 1 of twoNames
 set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
 
 if item 2 of twoNames = item 1 of twoNames then
 set {colNum2, rowNum2} to {colNum1, rowNum1}
 else
 set {colNum2, rowNum2} to my decipher(item 2 of twoNames)
 end if
 set nbRows to rowNum2 + 1 - rowNum1
 set nbColumns to colNum2 + 1 - colNum1
 
 else --****************
 
 (*
 Here we are in the second pass 
 We insert formulas grabbing the transposed cells *)
 
 set deci to character 2 of (0.5 as text)
 if deci is "." then
 set delim to ","
 else
 set delim to ";"
 end if
 
 set p2lproj to my getLproj("Numbers")
 set OFFSET_loc to my getLocalizedFuncName(p2lproj, "OFFSET")
 --set ADDRESS_loc to my getLocalizedFuncName(p2lproj, "ADDRESS")
 --set INDIRECT_loc to my getLocalizedFuncName(p2lproj, "INDIRECT")
 
 set {rName, tName, sName, dName} to my getSelection()
 if rName is missing value then error "No selected cells"
 
 set twoNames to my decoupe(rName, ":")
 set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
 (*
if item 2 of twoNames = item 1 of twoNames then
 set {colNum2, rowNum2} to {colNum1, rowNum1}
else
 set {colNum2, rowNum2} to my decipher(item 2 of twoNames)
end if
*)
 (* Here we know the starting point of the destination area. *)
 
 tell application "Numbers"
 activate
 tell document dName to tell sheet sName to tell table tName
 
 set rowsCible to (get row count)
 set rowsNeeded to rowsCible - 1 + nbColumns
 if rowsNeeded > rowsCible then
 repeat (rowsNeeded - rowsCible) times
 add row below row rowsCible
 end repeat
 end if -- rowsNeeded
 
 set columnsCible to (get column count)
 set columnsNeeded to columnsCible - 1 + nbRows
 if columnsNeeded > columnsCible then
 repeat (columnsNeeded - columnsCible) times
 add column after column columnsCible
 end repeat
 end if -- columnsNeeded
 
 repeat with i from 1 to nbRows
 repeat with j from 1 to nbColumns
 set value of cell (colNum1 - 1 + i) of row (rowNum1 - 1 + j) to "=" & OFFSET_loc & "(" & sourceSheet & " :: " & sourceTable & " :: " & sourceCell & delim & i - 1 & delim & j - 1 & ")"
 end repeat
 end repeat
 end tell -- table of sheet of document
 end tell -- Application
 
 set sourceCell to missing value
 set sourceTable to missing value
 set sourceSheet to missing value
 set nbRows to missing value
 set nbColumns to missing value
 end if
 
end run

--=====

on getSelection()
 local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
 tell application "Numbers"
 activate
 tell document 1
 set mySelectedRanges to selection range of every table of every sheet whose it is not missing value
 repeat with sheetRanges in mySelectedRanges
 try
 count of sheetRanges
 on error
 set sheetRanges to {sheetRanges}
 end try
 repeat with thisRange in sheetRanges
 if contents of thisRange is not missing value then
 try
 --return thisRange --poorly formed result
 thisRange as text
 on error errMsg number errNum
 set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)
 --set mySelection to (a reference to (range rn of table tn of sheet sn))
 return {myRange, myTable, mySheet, myDoc}
 end try
 end if -- contents…
 end repeat -- thisRange
 end repeat -- sheetRanges
 end tell -- document 1
 end tell -- application
 
 return {missing value, missing value, missing value, missing value}
end getSelection

--=====

on decipher(n)
 local letters, colNum, rowNum
 set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 if (character 2 of n) as text > "9" then
 set colNum to (offset of (character 1 of n) in letters) * 64 + (offset of (character 2 of n) in letters)
 set rowNum to (text 3 thru -1 of n) as integer
 else
 set colNum to offset of (character 1 of n) in letters
 set rowNum to (text 2 thru -1 of n) as integer
 end if
 return {colNum, rowNum}
end decipher

--=====

on decoupe(t, d)
 local l
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 return l
end decoupe

--=====

on getLproj(a)
 local lprojs, localId, lproj
 set lprojs to {{"da_DK", "da.lproj"}, {"nl_NL", "Dutch.lproj"}, {"en_US", "English.lproj"}, {"fi_FI", "fi.lproj"}, {"fr_FR", "French.lproj"}, {"de_DE", "German.lproj"}, {"it_IT", "Italian.lproj"}, {"ja_JP", "Japanese.lproj"}, {"ko_KR", "ko.lproj"}, {"no_NO", "no.lproj"}, {"pl_PL", "pl.lproj"}, {"pt_BR", "pt.lproj"}, {"pt_PT", "pt_PT.lproj"}, {"ru_RU", "ru.lproj"}, {"es_ES", "Spanish.lproj"}, {"sv_SE", "sv.lproj"}, {"zf_CN", "zh_CN.lproj"}, {"zh_TW", "zh_TW.lproj"}}
 
 set localId to my getLocale(a, "http://support.apple.com/en_US/manuals/#iwork")
 set localId to text (1 + (count of "http://support.apple.com/")) thru -1 of localId
 set localId to text 1 thru ((offset of "/" in localId) - 1) of localId
 
 set lproj to ""
 repeat with i from 1 to count of lprojs
 if localId is item 1 of item i of lprojs then
 set lproj to item 2 of item i of lprojs
 exit repeat
 end if
 end repeat
 
 if lproj = "" then error "A Frameworks file is missing !"
 return (path to application support as text) & "iWork '09:Frameworks:SFTabular.framework:Versions:A:Resources:" & lproj
end getLproj

--=====

on getLocale(a, x)
 tell application a to return localized string x
end getLocale

--=====

on getLocalizedFuncName(f, x)
 return localized string x from table "Localizable" in bundle file f
end getLocalizedFuncName

--=====

--[/SCRIPT]

Yvan KOENIG (from FRANCE dimanche 25 janvier 2009 13:41:45)

Thank you Mr. Koenig...this worked perfectly. In the past, the lack of a transpose function has been the only thing keeping me from switching from excel to numbers. Hopefully Apple will keep improving it in future versions so that you can copy and paste a transposed table. But in the meantime this works well. Thanks again Mr. Koenig!