We have a crosstab that stores product’s style data (width and length) and price information. The width data are stored in the first row, and the length data are stored in the first column:

Now we want to convert this table to a row-based table, and show product style as width*length, like this:

Script:

=spl("=E(?1).pivot@r(Style:Length;Width,Price).select(Price).sort(Width,Length).new(Width/""*""/Length:Style,Price)",Sheet1!A1:K16)

Perform the column-to-row conversion based on Style column, and give it a new name Length; the option @r means column-to-row conversion; the original column names are transferred and used as the values in the new column Width, and the original values in the cross cells are transferred and used as the values in the new column Price.
After that, select the rows whose value in column Price is nonnull, and sort them by Width and Length; create a new dataset, with “Width*Length” as the values in the new column Style, and take the Price column as the price column of the new dataset.
 

esProc Desktop and Excel Processing
13.2 Convert row-based table to crosstab
13.4 Interconversion of upper layer groups for rows and columns - column-to-row