TransposeArray ( array ; columnDelimiter )

Transposes a tabular text array. Requires the GetColumn () custom function.

Average rating: 4.2 (45 votes) Log in to vote

Michael Horak - Show more from this author
*COMMENT Visual Realisation

Sample input:

TransposeArray (
"1|2|3¶a|b|c¶x|y|z" ;
"|"
)

Sample output:

1|a|x
2|b|y
3|c|z

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
TransposeArray function

Author
*COMMENT Visual Realisation

Format
TransposeArray ( array ; columnDelimiter )

Parameters
array - any text expression or text field
columnDelimiter - any text expression or text field

Data type returned
text

Description
Transposes a tabular text array by turning the first column of the array into the first row of the new array, the second column of the array into the second row of the new array, and so on.

Requires the GetColumn () custom function.

July 8, 2008
*/

Let ( [
row1 = GetValue ( array ; 1 ) ; 
delims = PatternCount ( row1 ; columnDelimiter ) ;
column = GetColumn ( array ; delims + 1 ; columnDelimiter ) ;
nextRow1 = Left ( row1 ; Position ( row1 ; columnDelimiter ; 1 ; delims ) - 1 ) ;
nextArray = nextRow1 & ¶ & MiddleValues ( array ; 2 ; ValueCount ( array ) - 1 )
] ; 
Case ( 
delims ; 
TransposeArray ( nextArray ; columnDelimiter )  & ¶
)
&
Case ( 
ValueCount ( column ) ; 
Substitute ( Left ( column ; Length ( column ) - 1 )  ; ¶ ; columnDelimiter ) 
)
)

Transposes a tabular text array by turning the first column of the array into the first row of the new array, the second column of the array into the second row of the new array, and so on.

Requires the GetColumn () custom function:
http://www.briandunning.com/cf/877

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

TransposeArray ( array ; columnDelimiter )

Comments

Support this website.

Support this website.