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)
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...
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!
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!