DEV Community

Cover image for #138 — Convert Certain Columns of the Same Row, as Group Members, to Multiple Rows
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

2 1 1 1 1

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

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)
Enter fullscreen mode Exit fullscreen mode

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

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (1)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

💻 Transform your spreadsheets effortlessly with SPL XLL!

Discord
Reddit

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay