#134 — Convert Row-Based Table to Crosstab
Problem description & analysis: The following data table records the daily sales of products: Task: Now we want to create a crosstab, with the SaleDate as the left header of crosstab, and the Products as the upper headers of crosstab, like this: Solution: Use SPL XLL and enter the following code: =spl("=E(?1).pivot(SaleDate;Product,Amount)",Sheet1!A1:C16) Code explanation: Perform the row-to-column conversion based on SaleDate column, the values in the Product column are transferred and used as the new column names, and the values in the Amount column are transferred and used as the values in new columns. Download esProc Desktop for FREE and fast & dynamic Excel transformations!!
Problem description & analysis:
The following data table records the daily sales of products:
Task: Now we want to create a crosstab, with the SaleDate as the left header of crosstab, and the Products as the upper headers of crosstab, like this:
Solution:
Use SPL XLL and enter the following code:
=spl("=E(?1).pivot(SaleDate;Product,Amount)",Sheet1!A1:C16)
Code explanation: Perform the row-to-column conversion based on SaleDate column, the values in the Product column are transferred and used as the new column names, and the values in the Amount column are transferred and used as the values in new columns.
Download esProc Desktop for FREE and fast & dynamic Excel transformations!!
What's Your Reaction?
