How to Perform Inverse Transposition

The following is part of the Excel file source.xlsx:

1

2

3

4

5

6

7

8

9

11

22

33

44

Suppose the number of columns in the two-dimensional table is M. The task is to transpose the table into a new one having M rows in an inverse direction beginning from the Mth column. That is, M-1M-2…2,1.

The expected result:

4

7

11

33

44

3

6

9

22

2

5

8

1

It’s convenient to perform an inverse transposition with esProc. Download esProc installation package and free license file HERE.

1. Write script rvstrans.dfx in esProc:

A

B

1

=clipboard@e()

/ Read data in from   clipboard

2

=A1.split@n("\t")

/ Return strings as a   sequence of sequences

3

=transpose(A2)

/ Perform transposition over   A2’s sequence

4

=A3.rvs()

/ Reverse the direction of   members in A3’s sequence

5

=A4.concat@n("\t")

/ Concatenate sequence   members in A4’s sequence as a string

2. Select the source data area A1:D5 in the Excel file, press Ctrl+C to paste it onto the clipboard. Open esProc IDE, enter and execute the above script. A5 is the final result. Then we copy A5’s value, open Excel, select cell A7 and press Ctrl+V to paste the result in.

Copy the final result in A5:

undefined

Paste the result back to Excel:

undefined

If there are specific requirements about the final result, such as every two of the rows need to be separated by two empty rows, you can directly modify the final result string using =replace(A5,"\n","\n\n\n"). The expression means replacing the one line break by three line breaks.