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.
Top comments (4)
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!
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...
What a nice little tip! Thanks!
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!