Concatenate Column Values and Perform Grouping & Aggregation

Concatenate Column Values and Perform Grouping & Aggregation

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.

original table

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:

desired table

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:

desired result table with code entered

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.


Discover more from Coursity

Subscribe to get the latest posts sent to your email.

Leave a Comment

Comments

No comments yet. Why don’t you start the discussion?

    Leave a Reply

    Your email address will not be published. Required fields are marked *