#134 — Convert Row-Based Table to Crosstab

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

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!! 🚀✨⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

Print Share Comment Cite Upload Translate Updates

APA

Judith-Excel-Sharing | Sciencx (2025-01-21T09:39:10+00:00) #134 — Convert Row-Based Table to Crosstab. Retrieved from https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/

MLA

" » #134 — Convert Row-Based Table to Crosstab." Judith-Excel-Sharing | Sciencx - Tuesday January 21, 2025, https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/

HARVARD

Judith-Excel-Sharing | Sciencx Tuesday January 21, 2025 » #134 — Convert Row-Based Table to Crosstab., viewed ,<https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/>

VANCOUVER

Judith-Excel-Sharing | Sciencx - » #134 — Convert Row-Based Table to Crosstab. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/

CHICAGO

" » #134 — Convert Row-Based Table to Crosstab." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/

IEEE

" » #134 — Convert Row-Based Table to Crosstab." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/. [Accessed: ]

rf:citation

» #134 — Convert Row-Based Table to Crosstab | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2025/01/21/134-convert-row-based-table-to-crosstab/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.