#138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows

Problem description & analysis:

Here below is a data table:

Task: Now we want to convert it to a row-based table, like this:

Solution:

Use SPL XLL and enter the following code:

=spl(“=E(?1).pivot@r(Names,Sales;Weekday…


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

Problem description & analysis:

Here below is a data table:

source table

Task: Now we want to convert it to a row-based table, like this:

expected results

Solution:

Use SPL XLL and enter the following code:

=spl("=E(?1).pivot@r(Names,Sales;Weekday,Day).new(Names,Sales,Day).select(Day)",Sheet1!A1:G5)

Code explanation: Perform the row-column transposition on the data. The option @r means column-to-row. The Names and Sales columns are used as the base column, other column names are transferred and used as the values in the Weekday column, and the values in other columns are transferred and used as the values in column Day.
Select the columns Names, Sales and Day, and the rows whose value in column Day is nonnull.

Download esProc Desktop for FREE and boost productivity today!!! 🚀✨⬇️

✨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-27T05:15:04+00:00) #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows. Retrieved from https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/

MLA
" » #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows." Judith-Excel-Sharing | Sciencx - Monday January 27, 2025, https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/
HARVARD
Judith-Excel-Sharing | Sciencx Monday January 27, 2025 » #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows., viewed ,<https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/>
VANCOUVER
Judith-Excel-Sharing | Sciencx - » #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/
CHICAGO
" » #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/
IEEE
" » #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/. [Accessed: ]
rf:citation
» #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2025/01/27/138-convert-certain-columns-of-the-same-row-as-group-members-to-multiple-rows/ |

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.

Related Posts