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

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (1)

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