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-1，M-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:
Paste the result back to Excel:
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
