Concatenate Column Values and Perform Grouping & Aggregation

#programming #beginners #tutorial #productivity

Problem description & analysis:

In the table below, the 1st column is person’s name, and the multiple columns after it are items they purchased. There are people who sometimes buy multiple same items in one purchase and who place multiple orders at different times.

We need to rearrange the table into a crosstab, where the column headers are items and the row headers are people’s names, as shown below:

Solution:

Use SPL XLL to do this:

=spl("=?.groupc@r(~1;~.m(2:);1).pivot@s(~1:Name; ~2,count(~2))",A1:D5)

As shown in the picture below:

Explanation:

groupc@r groups members of a sequence by a specified number and transposes columns to rows; ~1 represents the 1st child member of the current member, and ~.m(2:) gets child members of the current member from the 2nd to the last. pivot@s transposes rows to columns and performs aggregation on each group of data.

Python + AI + Spreadsheet

Chat with your data and get insights in seconds with the all-in-one spreadsheet that connects to your data, supports code natively, and has built-in AI.

Try Quadratic free

Top comments (2)

Judith-Data-Processing-Hacks • Jul 8 '24

You can also visit our Discord and Reddit communities to get more solutions to difficult Excel tasks! The experts there are generous enough to offer a hands! 🙌🏻 discord.gg/hgbKEvJ4
🙌🏻 reddit.com/r/esProc_Desktop/