#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.

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:

Solution:

Use SPL XLL and enter the following code:

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

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