#133 — Row-to-Column Conversion for Fixed Columns
Problem description & analysis: Here below is a data table: Task: Now we want to convert the table to the form as shown in the figure below, and list the scores in the order of Chinese, Maths and English. And the expected results are as follows: Solution: Use SPL XLL and enter the following code: =spl("=E(?1).pivot(ID,Name;Subject,Score; ""Chinese"",""Maths"",""English"")",Sheet1!A1:D13) Code explanation: Perform the row-to-column conversion based on columns ID and Name. The values in the Subject column are transferred and used as the new column names, the values in the Score column are transferred and used as the values in the new columns, and the new column names are arranged in the order of “Chinese”, “Maths”, “English”. Download esProc Desktop for FREE and eliminate manual errors using SPL XLL!!
Problem description & analysis:
Here below is a data table:
Task: Now we want to convert the table to the form as shown in the figure below, and list the scores in the order of Chinese, Maths and English. And the expected results are as follows:
Solution:
Use SPL XLL and enter the following code:
=spl("=E(?1).pivot(ID,Name;Subject,Score; ""Chinese"",""Maths"",""English"")",Sheet1!A1:D13)
Code explanation:
Perform the row-to-column conversion based on columns ID and Name. The values in the Subject column are transferred and used as the new column names, the values in the Score column are transferred and used as the values in the new columns, and the new column names are arranged in the order of “Chinese”, “Maths”, “English”.
Download esProc Desktop for FREE and eliminate manual errors using SPL XLL!!
