DEV Community

Cover image for #137 — Re-group or Sort When Filling Grouped Data into Columns
Judith-Data-Processing-Hacks
Judith-Data-Processing-Hacks

Posted on

2 1 1 1 1

#137 — Re-group or Sort When Filling Grouped Data into Columns

Problem description & analysis:

There is a data table. In this table, the products with the same name may have multiple colors.

source table

Task: Convert the data to the form as shown below, that is, arrange each type of product in one row, and then list the Name and Color of each product in turn. The results are shown below:

expected results

Solution:

Use SPL XLL and enter the following code:

=spl("=E(?1).group(Type).(~.group(Name)).((~.Type|~.(Name|~.(Color))).conj@r())",Sheet1!A1:C8)
Enter fullscreen mode Exit fullscreen mode

Code explanation:

First group by Type, and then group by Name in each group.
Loop through groups of Type. First take Type, and then add the sequence composed of Name and Color of each group in its Name subgroups, and finally use conj to concatenate the sequence. Option @r means recursive calculating until all members are no longer a sequence.


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

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Eliminate Context Switching and Maximize Productivity

Pieces.app

Pieces Copilot is your personalized workflow assistant, working alongside your favorite apps. Ask questions about entire repositories, generate contextualized code, save and reuse useful snippets, and streamline your development process.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay