13.10 Convert groups to columns after grouping
Here below is a data table with two columns (Car and Color):
Now we want to convert the data to the following form with the car type as column name, and list all colors of each type of car and remove the duplicate color:
Script:
| A | |
|---|---|
| 1 | =E(‘Sheet1!A1:B18’) |
| 2 | =A1.group(Car).(Car|~.id(Color)) |
| 3 | =A2.max(~.len()) |
| 4 | =A2.(~.pad(null,A3)) |
| 5 | =transpose(A4) |
A2: Group by Car. In each group, form a sequence with car name and its distinct colors. ~.id(Color) means taking the color that is unique in this group
A3: Calculate the maximum length of all grouped sequences
A4: Use null value to complement the sequence of each group to the maximum length for transposing
A5: Transpose rows and columns of A4
esProc Desktop and Excel Processing
13.9 Convert group formed by every N columns to multiple rows
13.11 Rearrange multiple columns into a cross-tab
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 http://c.scudata.com
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
