Whether you work in or are preparing data for analysis, market research or auditing, or simply need to create unbiased samples for statistical studies, the need to select X number of rows at random from a Table is a common requirement for Excel users.
In this video I demonstrate one way to perform this task, using a combination of functions… ROWS, RANDARRAY and CHOOSEROWS (these last 2 are only available in Excel 365).
I also provide a simple method to ensure that the randomly generated data contains no duplicates.
The formulas that I used in the demos:
=ROWS(Sales)
=ROUNDUP(L125%,0)
=RANDARRAY(L2,1,1,L1,TRUE)
=CHOOSEROWS(Sales,M1#)
=COUNT(UNIQUE(M1#))
=IF(L2=L3,”OK”,”Problem”)
Blog post with long convoluted formula to avoid duplicates: https://www.techrepublic.com/article/how-to-generate-random-numbers-with-no-duplicates-in-excel/