#135 — Convert Crosstab to Row-Based Table

#programming #beginners #tutorial #productivity

Problem description & analysis:

We have a crosstab that stores product’s style data (width and length) and price information. The width data are stored in the first row, and the length data are stored in the first column:

Task: Now we want to convert this table to a row-based table, and show product style as width*length, like this:

Solution:

Use SPL XLL and enter the following code:

=spl("=E(?1).pivot@r(Style:Length;Width,Price).select(Price).sort(Width,Length).new(Width/""*""/Length:Style,Price)",Sheet1!A1:K16)

Code explanation:

Perform the column-to-row conversion based on Style column, and give it a new name Length; the option @r means column-to-row conversion; the original column names are transferred and used as the values in the new column Width, and the original values in the cross cells are transferred and used as the values in the new column Price.
After that, select the rows whose value in column Price is nonnull, and sort them by Width and Length; create a new dataset, with “Width*Length” as the values in the new column Style, and take the Price column as the price column of the new dataset.

Download esProc Desktop for FREE and level up your Excel skills now!! 🚀✨⬇️

✨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