DEV Community

Cover image for #35 - Get A Random Row from Each Group
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

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

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.

Top comments (4)

Collapse
 
judith677 profile image
Judith-Excel-Sharing • Edited

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!

Collapse
 
judith677 profile image
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...

Collapse
 
martinbaun profile image
Martin Baun

What a nice little tip! Thanks!

Collapse
 
judith677 profile image
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!