#35 - Get A Random Row from Each Group

Judith-Excel-Sharing - Jul 29 - - Dev Community

Problem description & analysis:

In the Excel table below, column A is the grouping field and column B contains detail data.

original table

We want to get a row from each group randomly.

desired table

Solution:

Use SPL XLL to do this:

=spl("=E(?).sort(rand()).group@1(Group)",A1:B31)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered

Explanation:

E()function parses the range as a table. sort(rand()) arranges rows randomly. group@1 groups rows and gets the 1st record from each group.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .