#35 - Get A Random Row from Each Group

Problem description & analysis:

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

We want to get a row from each group randomly.

Solution:

Use SPL XLL to do this:

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

As shown in the picture below:

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.