#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.

Comments

[Judith-Excel-Sharing]

If you’re interested in more tips and tricks or need help with specific Excel tasks, feel free to reach out:
🍉Discord: discord.gg/hgbKEvJ4
🍉Reddit: reddit.com/r/esProc_Desktop/
Your problem might even be featured in our ExcelHack series!

[Judith-Excel-Sharing]

For more related information, please download SPL XLL for free:

🔺SPL download address: scudata.com/download-Desktop

🔺Plugin Installation Method: c.scudata.com/article/1652061135502

🔺References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

🔺SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

[Martin Baun]

What a nice little tip! Thanks!

[Judith-Excel-Sharing]

Thank you so much! I’m glad you found the tip useful. Your support means a lot. If you have any other questions or need more tips, feel free to ask!